First non blank cell value above formula.

[VytautasM]

Good day,

I have a column of data, and I need to find the previous non-blank cell value. For example, if I have the following data:

book
need to find A1 (book)
car
need to find A5 (car)
dog
need to find A8 (dog)
need to find A9 (dog)

I want the (formula) cell to find a reference to the cell containing "book" no matter how many blank cells are inserted between them. It is possible that new rows with blank cells in the column could be inserted between the formula row and the previous non-blank row at any time, and the formula should be able to handle that.

Ideally, I would like to be able to put the formula in any cell on a row to find the nearest non-blank cell above that row in, say, column A.

Tried many option, but non seem to work for my needs.

Any suggestions would be greatly appreciated.

 

[Fluff]

How about

+Fluff New.xlsm
	A	B
1
2	Book	Book
3		Book
4		Book
5		Book
6	Cat	Cat
7		Cat
8		Cat
9	Dog	Dog
10		Dog
Data

Cell Formulas
Range	Formula
B2:B10	B2	=LOOKUP(2,1/(A$2:A2<>""),A$2:A2)

 

[VytautasM]

How about

+Fluff New.xlsm
	A	B
1
2	Book	Book
3		Book
4		Book
5		Book
6	Cat	Cat
7		Cat
8		Cat
9	Dog	Dog
10		Dog
Data

Cell Formulas
Range	Formula
B2:B10	B2	=LOOKUP(2,1/(A$2:A2<>""),A$2:A2)

Fluff,

I need the formula to go in the same column as the values. In your example the formula would need to good in A3, A4, A5 and read book, in A7, A8 and read cat, in A10 and read dog.

 

[Fluff]

You can still use that formula, just put it in whatever cells you want.

 

[VytautasM]

Your right, sorry about that.

And thank you for a fast solution.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Slainte]

How about

+Fluff New.xlsm
	A	B
1
2	Book	Book
3		Book
4		Book
5		Book
6	Cat	Cat
7		Cat
8		Cat
9	Dog	Dog
10		Dog
Data

Cell Formulas
Range	Formula
B2:B10	B2	=LOOKUP(2,1/(A$2:A2<>""),A$2:A2)

You just saved me hours and hours of work. I might actually get to go to sleep tonight. Thank you sooo much.