find last value in a "moving" range Excel 2007

[sedgely]

Hi All

i have a range of cells in Excel currently C16:F16, i use the following formula to return the value in the last populated cell from that range
=LOOKUP(2,1/($C16:$F16<>""),$C16:$F16)
this works great, however the range is going to be expanding i.e. each week a new column will be inserted and new values added
as an added complication the formula sits in the column directly after the range (in the example above the formula would be in G16) but the new column may not contain data in which case i need the value from the previous column
anyone got any bright ideas how i can achieve this

Cheers, Craig
Si fractum non sit, noli id reficere

 

[SkipVought]

Hi,

Create a dynamic range name for your data area. faq68-1331

I created one named DataArea.

My formula...
[tt]
=INDEX(DataArea,16,COLUMNS(DataArea))
[/tt]
assuming the DataArea starts in row 1 and continues thru row 16.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]