Reference a cell in the previous visible row

Question

How can I reference a cell in the same column in the previous visible row from a filtered range?

Commodus

Answer 1

This is very easy if we prepare to do it.

Say we have data like:

The first step is to introduce a "helper" column. In E2 we enter:

=IF(SUBTOTAL(3,A2:A2)=0,MIN($E$1:E1)-1,SUBTOTAL(3,$A$2:$A2))

and copy down:

Now the cool thing about the "helper" column is that no matter how you filter it, it always shows a simple sequential sequence....let's filter for Alice

This means that any cell can:

1. retrieve the value in column E
2. subtract 1 from it
3. use this value in a Index()/Match() formula to retrieve any value in the previous visible row

EDIT#1:

To answer your question, in C5 we enter:

=INDEX($C$2:$C$21,MATCH(E5-1,$E$2:$E$21,0))

and with no filtering C5 will display $391.00
But with Alice filtering C5 will display $446.00

Nuthouse

I tried to use Index/Match, but without a success, how can I find the coordinates of the above cell? For example, in your last image, imagine that c5 needs a reference to its above cell, in this case c2 – Commodus

Answer 2

I had the same problem and managed to find a solution (with a lot of help from a GPT). Here is my solution to find the correct visible row with the right cell.

Purpose: The goal is to retrieve the value from the cell in column G that is directly above G3 and is visible, considering any filters that may be applied. If G2 is visible, the formula should return the value from G2. If G2 is hidden and G1 is visible, it should return the value from G1.

=INDEX(G$1:G2; MAX(IF(SUBTOTAL(3; OFFSET(G$1; ROW(G$1:G2)-MIN(ROW(G$1:G2)); 0; 1)); ROW(G$1:G2))))

Note: This formula must be entered as an Array Formula. To do this, press Ctrl + Shift + Enter after typing the formula. Excel will then automatically place curly braces {} around the formula.

Breakdown of the Formula: G$1:G2: This specifies the range in column G where we are looking for the highest visible row above G3.

MAX(IF(...)): This portion of the formula finds the highest visible row number in the specified range (G1:G2).

SUBTOTAL(3; OFFSET(...)): The SUBTOTAL function with the function number 3 is used to check if rows are visible. The OFFSET function helps in adjusting the rows within the specified range.

ROW(G$1:G2): This returns the row numbers of the cells in the range G1:G2.

INDEX(G$1:G2; MAX(...)): Finally, the INDEX function uses the row number returned by the MAX function to retrieve the value from the correct cell in the range G1:G2.

Example: If G2 contains a value, such as 100, and is visible, and G1 is hidden, the formula will return 100. If G2 is hidden and G1 is visible, it will return the value from G1. This formula is useful when working with filtered data, where only specific rows are visible, and you want to dynamically reference the visible data just above a certain row in the column.

Danner