Hi
using array formulas (entered with CTRL+SHIFT+ENTER):
=INDEX(A1:A150,MIN(IF(B3:B150="*",ROW(B3:B150))))
and
=INDEX(A1:A150,MAX(IF(B3:B150="*",ROW(B3:B150))))
--
Regards
Frank Kabel
Frankfurt, Germany
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
|