equals last cell with info formula

[rharri1972]

Hello! On sheet 2 cell A1 i need a formula to retrieve data from last cell that has been used in Column C on Sheet 1. Column C data starts on C4 and runs through C27. I know this can be done in VBA but is there a formula that can do this? Data changes once a week and would need Cell A1 on Sheet 2 to update with new cell data.

I hope this makes sense.

Any help is greatly appreciated

 

[awoohaw]

try this:

=INDEX(Sheet1!$C$1:$C$100,MAX((Sheet1!$C$1:$C$100<>"")*(ROW(Sheet1!$C$1:$C$100))))

 

[rharri1972]

try this:

=INDEX(Sheet1!$C$1:$C$100,MAX((Sheet1!$C$1:$C$100<>"")*(ROW(Sheet1!$C$1:$C$100))))

THANK YOU WORKS GREAT!!

 

[awoohaw]

Happy to help.

Best Wishes!

 

[Peter_SSs]

Depending on your circumstances, you may be able to use something simpler.

If column C on Sheet1 is not populated by formulas that could return "" & contains text values, you could try something like this.

=LOOKUP("zzz",Sheet1!C:C)

If column C on Sheet1 contains numerical values, you could try something like this.

=LOOKUP(9^9,Sheet1!C:C)