Select the highest cell, but cell could be the lowest value or the highest

[Jaffabfc]

Morning,

I am wanting to select a cell in a column, for example

B2=10
B3=
B4=
B5= 400
B6= 2
B7=
B8=6

So even though B6 is the lowest range and B5 is the highest i would like to pull whatever is in cell B8.

These cells are forever expanding so how can i do this?

I am pulling this information from another spreadsheet which i can not edit so i can not put something in column A or C to make it easier, i can not sort it either.

thanks

 

[Swapnil Shah]

So , if you mean that you want the last cell's value by formula you can use the following formula

=INDEX(B2:B8,ROWS($B$2:B8))

if you need last nonempty cell's value , you can use following

=INDEX(B:B,MAX(IF((B:B)<>"",ROW(B:B),"")))

Both formulas are array one and you need to Press Ctrl+Shift+Enter and not only enter


If this is not what you want , you can elaborate your requirement further.


Thanks
Swapnil Shah

 

[Jaffabfc]

Exactly what i wanted Thankyou Very Much