Excel Cell Referencing 1

[renesp]

Hi all and thanks for helping out. I need to have a cell on Sheet1 keep referencing the latest cell of column F Sheet2. So as the worksheet grows,I know the what the latest information on the other worksheet is.

Thanks again.

 

[SkipVought]

Either fo these work...
[tt]
=OFFSET(Sheet2!A1,COUNTA(Sheet2!F:F)+5,5)

=INDEX(Sheet2!F:F,COUNTA(Sheet2!F:F)+6,1)
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[byundt]

If you know that you are looking for a numeric value (number, date or time), then you could use:
=LOOKUP(1E308,Sheet2!F:F)

If you know that you are looking for text (including text that looks like numbers), then you could use:
=LOOKUP("zzzzz",Sheet2!F:F)

Either of these LOOKUP formulas will return the last value entered in column F provided that it is the same type of value (number or text) as the first parameter. LOOKUP will ignore blank cells and error values.

The reason for the screwy values in the first parameter is that LOOKUP assumes the data in column F is sorted. You and I know that it is not, so we fool LOOKUP by passing a parameter that will sort higher than any possible value in column F. LOOKUP can't find it, and returns the very last value that it did find.

Brad

 

[renesp]

Thank you very much for all your help. The INDEX feature worked well.