excelvbaexcel-formulaexcel-2010

Reference a cell in the previous visible row

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

Solution

  • This is very easy if we prepare to do it.

    Say we have data like:

    enter image description here

    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:

    enter image description here

    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

    enter image description here

    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