One way, for top
=INDEX(A1:A1000,MATCH("~*",B1:B1000,0))
for bottom
=INDEX(A1:A1000,MAX((B1:B1000="*")*(ROW(B1:B1000)) ))
the latter entered with ctrl + shift & enter
Regards,
Peo Sjoblom
"RFJ" wrote:
In each cell of column B (rows 3 to 150), there is either nothing (Null) or
an asterisk. All the asterisked cells will be in consecutive rows - but
where they start and finish is variable depending on other calculations in
the worksheet.
I want to be able to find the top and bottom row that has an asterisk in it
and return the corresponding cell values in column A.
eg
a
b
c *
d *
e *
f *
g
h
I'd be looking for two formulae - one to identify 'c' as the top value and
'f' as the bottom value.
Can SKS help.
Many thanks in advance.
Rob
|