Formula to look in adjacent cell, then look upwards till a nonblank cell is found

What I want is a formula that will look to the cell to the left and if the cell is blank, to look into the cell above that and continue up until it finds a non blank cell and returns the text contained. If all cells are blank, the assumed text would be "All". I am wondering if I could do this without having to nest a bunch of If statements it will end up being numerous nestings. The purpose is to put the formula into the Indirect function for data validation purposes.

The example shows in the columns the values that would be returned if there was the working function in the yellow section.

Please Login or Register  to view this content.

insert this formula in c2 and copy it down the c column as needed

This will give you "All" if B2 is empty, up until a name appears in column B:

=IF(B2="",IF(C1="","All",C1),B2)

Put it in C2 and copy down.

Hope this helps.

Pete