Search results

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...

If you are using a macro to do the work, make sure that autocalculation is disabled when the macro starts and is enabled when it finishes. This should speed up the analysis fantastically.

you can format the cell with a diagonal line and then put a couple of Char(10) in the formula to give you line feeds which will split the information either side of the diagonal line.

You are limited to 7 IFs unless you are using 2007. There are workarounds using LOOkUPs or named formulae or try restructuring the formula. Post an an example of what you want to achieve or of your workbook

Sounds like the sheet is protected and the one cell you describe is the only one that is unlocked. Try going to Tool protection > unprotect sheet

This is a good site to post on www.excelforum.com