how do i invert information in a column

[Pivotrend]

i have information in column B
this information gets updated all the time
it could be from B4:B1000 or more , it's not fixed
say to B65536
how do i show the last cell information in cell C4
i mean show the information up side down
not to forget that information is updated & the last cell could be B1000 or B65536
how to do that using a formula Only ?

example attached

[VBA Noob]

Hi,

Try

=LOOKUP(2,1/(B1:B65535<>""),B1:B65535)

VBA Noob

[Pivotrend]

Hi,

Try

=LOOKUP(2,1/(B1:B65535<>""),B1:B65535)

VBA Noob

didn't Work

only the last cell showup
but the second is = B5

i want cell B1260 to showup in C4
& B1259 to showup in C5
& so on

not to forget that information gets updated all the time & the last cell could be B1000 or B65536

!!!

[VBA Noob]

Does this array help. Enter with CSE (Ctrl + shift + Enter)

=INDEX($B$1:$B$65535,LARGE(IF($B$1:$B$65535<>"",ROW($B$1:$B$65535)),ROW(C1)))

VBA Noob

[Pivotrend]

worked fine
thank you VBA Noob

[VBA Noob]

No problem

VBA Noob

[Jon]

Does anyone know whether this could be put in to a function?

I'd like the same thing; but if I could highlight the data, then click on a button that would be tasty!