Lookup problem

[Karen123]

Hello

I'm creating a table i want to update with the latest data. Part of the data will show % satisfaction for the last three months. I need the month names to update automatically. I've added a cell (B2) which will be updated with the latest month.

'This month' info will then update in the table. However, if the latest month is July, i want the table headings next to the 'July/Current month' column to show the previous month and the month before that.

The best solution i could think of was a lookup, so i've created a hidden table. List of months in (J), the corresponding previous month in (K) and the month before that in (L). The formulas on the three months columns are:

Current month column - =B2
Previous month column - =LOOKUP(B2, j:j,k:k)
2 months previous - =LOOKUP(B2,j:j,l:l)

However, only the current month is updating with these formulas. Can anyone help?

Thanks

 

[baitmaster]

try keeping the values as actual dates, and change the formatting

You can use =TODAY(), formatted as mmm to show this month

EOMONTH(today(),-1) will give last day of last month, again formatted as mmm only will just show last month. Use -2 for month before...

 

[Karen123]

Hello

Thanks very much for getting back to me. I couldn't get excel to recognise the month(s) previous formulas, but i typed it into google and i think it's because i have an older version of excel. This alternative was suggested on another forum those affected:
=DATE(YEAR(NOW()),MONTH(NOW())-1,0)

Thanks again