HLOOKUP with current date problems

[Kentona]

I currently have a spreadsheet which has a number of different accounts on each tab and the front tab I have set up as an overview, which has the current totals for every account for each month of this year. I have the headings set up as mmm-yy.

What I am trying to achieve is to have a column at the end of this overview and just the figures for the current month so that they stand out more and have a HLOOKUP on the figures to the left of it. At the top of this column I have got the current date by using Ctrl+; and also have it formatted to mmm-yy, but the HLOOKUP doesn't recognize it. It does work if I override it with 01/07/07 (for Jul-07). I'm guessing this is because the dates at the top of each the column I used 01/01/07, 01/02/07 etc.

I am using the Windows XP version - does any one have any ideas and I can get this to work?

Thank you in advance.

 

[barry houdini]

Rather than using the current date, use this formula to give the 1st of the current month

=TODAY()-DAY(TODAY())+1

This will give 1st July 2007 until the end of this month and then change, automatically, to 1st August 2007 etc. You can format it however you want

 

[Kentona]

That’s great – thank you very much.