Recent content by qafself

What do you mean by consolidate? An example would help.

I suspect that the problem is that the data is not sorted. You probably need to use VLOOKUP, using the parameter false. As an aside you do not need to use the concatenate function - you can just put =Value1&value2&value3..... that said, it's not clear how your data is arranged - suggest you...

can you rearrange the data so that names go down and grades go across? That would make it simple

In that case I suggest that you copy all data from sheet, delete sheet and create a new one - excel does go funny on you sometimes and if a particular sheet is problematical the best solution is to bin it.

Suggest you remove the data validation settings on that sheet and redo them it won't take long if you tick the check box for apply this to all cells with the same seting.

Yes - you're right, retirement is sweet -I get my bus pass this year and a bit more pension!

No Problem Oddpro - thanks for letting me know you are now sorted out. Brian - you need more practice with SUMPRODUCT - I seem to remember I introduced you to the beauties of it several years ago........

see attached

If you are using 2007 you could use COUNTIFS instead of SUMPRODUCT

How about something like this in summarised!B2 =SUMPRODUCT(--(Data!$B$2:$B$133>=Summarised!$B$1)*(Data!$B$2:$B$133<Summarised!$C$1)*(Data!$C$2:$C$133=A2)) This sums the occurances where the date is greater than or equal to the date in the col head and the date is less than the col head of the...

Check out SUMPRODUCT http://www.xldynamic.com/source/xld.SUMPRODUCT.html

You could use conditional formatting to colour alternate rows - make reading across much easier

Why not just filter on the letter "Y"

Use SUMIF - Excel help will explain the detail

Yes, set the currency to two decimal places and then use round(cell ref,0) to get rid of pence. Note - using round means that you are changing the value stored, but when the currency is set to zero decimal places, the value stored is the calculated value not the displayed value. If you are...