show current value

[Guest]

Hope someone can help
I have a sheet linked to a chart linked to the PowerPoint report.
The sheet column B-M is the months
Row 2-7 is years (2005-2010)
The data is then put into a chart
In Cell I21 I have =IF(K3>2000,"CLVs Met","CLVs Not Met")
K3 is Oct 06 and has a value of 2100 so I21 comes back as CLVs Met
Question can I have I21 show (change) to the newest data that was put into
another cell without changing the cell (I21) each time?
So if I put in a value of 1200 into L3 (Nov 06) I21 will show “not metâ€
Thanks
James

 

[Guest]

One way ..

Put in I21, then array-enter** the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(INDEX(K3:IV3,MATCH(MAX((K3:IV3<>"")*COLUMN(K3:IV3)),(K3:IV3<>"")*COLUMN(K3:IV3),0))>2000,"CLVs Met","CLVs Not Met")

**Done correctly, Excel will wrap curly braces: { } around the formula (look
for these braces in the formula bar as a visual check after you array
-enter). If you don't see these braces, then the array-entering wasn't done
correctly.

The INDEX part of the formula will grab the rightmost cell within the range
K3:IV3 which contains the monthly number which was input (inputs are from K3
across), ie the latest month's number. The value is then used in your IF
comparison.