[SOLVED] Find most recent valid cell and return its value

Hello, I'm have what might be a rather basic question for some more experienced members. I don't have much experience yet with index or lookup functions, and I'm looking for some advice.

I am writing Conditional Formatting rules to draw borders around groups of cells in a table, to visually separate them from one another. I'm having trouble with the top border. Some cells are empty or contain empty strings, so I want to only check those containing a number.

Here's what I want to achieve:

From the top of this column to the cell above the current cell, find all cells with a number value.

Of those, find the one with the highest row number.

If it holds a value which does not equal the current cell's, apply the condition.

I think it should look roughly something like this, but I don't have the right syntax because I haven't fully understood how each part works:

Please Login or Register  to view this content.

Could someone advise me on how to correctly combine these or similar functions to achieve the required result?

Attach a samlple worksheet. See instruction at top of page in yellow banner.

Instead of trying to edit row with top border, try to use another CF for blank row with bottom border
Stay in cell B2:
=AND($B2="",$B1 <> $B3,$B2 <> $B3,$B3=1)

In attachment, I test for Unit 1.
Add more with unit 2,3,4,5...

Thanks for your reply. It's a good idea, I hadn't thought of testing for blank rows rather than numbered rows! I'll try it out. I'm concerned about cases where there may be more than one blank row together in the middle of a unit, but if I run into problems I'll come back and update.

UPDATE:
Your idea worked! The top borders work well. However, I'd like to be able to address cases where there is an interruption of more than one row in a unit, so I'd still like to know the answer to my original question.

How can I find the previous cell in a column with a number value, and check it against the next cell in the column with a number value? Do you have any ideas?

Thanks again for your help!

UPDATE:
I cracked it!

Here is the solution I found to the above problem in bold:

Please Login or Register  to view this content.

• IF(ISNUMBER(B:B),ROW(B:B)) returns an array of row numbers of those cells in column B that contain numbers.
• Wrapping that in a MIN or a MAX returns the lowest or highest row number in that array.
• Wrapping that in a MIN(1, ...) omits zeroes to avoid REF! errors.
• Wrapping that in INDIRECT("$D"&...) gives the value in the cell in column D in that row.

In order for this formula to work in a cell, because of the arrays, it needs to be wrapped in curly brackets {=FORMULA(...)}. As a Conditional Formatting rule, this isn't necessary.

In practice, I don't need to compare the two phrases directly. I just compare $D2 to each half for each CF rule, the top and bottom borders.

Please Login or Register  to view this content.

You can see my solution in the example attached.

If you have a better, more elegant solution, please share it!