Hi,

Suppose the data is in range F17:F22. In cell G17, use the following
formula and copy down

=IF(ISERROR(LOOKUP(REPT("z",99),F$16:F16)),"",LOOK UP(REPT("z",99),F$16:F16))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Kris W" wrote in message
...
Is there a way to reference the nearest cell above the current cell that
has
text - distance is always going to be variable.