Preceding non-blank cell 2

[bigoldbulldog]

Does anyone know how to find the first preceding non-blank cell in a column based on some starting reference address? I'd like to do this using Excel built-in functions, not VBA (yeah, vba would be simple).

Cheers,
ND

 

[SkipVought]

Hi,

Are you finding a REFERENCE or finding a CELL?

Check out END DownArrow to navigate to the end of a contiguous range.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[AnotherHiggins]

This might not be what you want, but to navigate to that cell would just be [Ctrl]+[Up Arrow].

Otherwise, let me confirm what you're after....

Example:

Col A
01 First
02 Second
03
04
05 Third
06 Fourth
07
08
09
10 Fifth
_____

So if you started with A10, you would want "Fourth" returned? Or A6?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[bigoldbulldog]

I'm trying this as cell's formula.

I'd like the A6 field's value.

Cheers,
ND

 

[SkipVought]

=A6

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[AnotherHiggins]

How about a User-Defined Function? All the ease of VBA, but you can use it like a function in the worksheet.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Hi ND:

Let me see if I have understood you correctly ...

I have assumed you want to find the first non-blank cell down from cell A5.

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[bigoldbulldog]

Yogi's solution does what I hoped. But instead of the performance hits of copying calculating down multiple cells and dealing with array-based formulas I've settled for the easier conditional formatting trick to match the font color to the background. Why do I think of harder solutions first?

Anyway I'll be tucking the great solution and other ideas away for other applications.

Thanks to all!

Cheers,
ND

 

[AnotherHiggins]

Very cool, Yogi. ->

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[yogia]

Thanks John! ... now let us keep EXCELing.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com