1

How can I get a reference to the first visible cell above the active one after the data in a table has been filtered.

NOTE: I don't need this for summing up the data above. I know how to do that on filtered data.

This is more complicated. What I'm actually making is a function which would allow me to alternate row colors of duplicate data similar to (Alternate Row Colors of Duplicate Data in Excel) but it needs to be able to work on filtered data.

Improve this question
3
  • Are you looking for banded rows of non-sequential rows (because of the filtering) or is there some criteria that determines the row color? Please tell us what you have researched and attempted to resolve this so far.
    – CharlieRB
    May 12, 2015 at 11:35
  • At the moment I using verry similar approach that is shown in the question that I linked. The problem with that approach is that it works correctly only on unfiltered data. If I filter the data the lines are not colored as I would want. So the only thing that I need to make it so is a way to reference the first shown cell above so I can modiffy the formulat that is used to determin which color should be used to use relative referencing to only visible cells when checking the data and not absolute referencing as it does now.
    – SilverWarior
    May 12, 2015 at 12:42
  • About the only way I know to get info about visibility of a row is to use something like SUBTOTAL(103, A1)>0, where A1 is a cell in the row of interest. SUBTOTAL and AGGREGATE are two functions that will count with an option to only count visible data. 103 above is the COUNTA function for visible cells only. You can use that function to determine visibility and then possibly rig up a conditional formatting looking for it. See support.office.com/en-in/article/…
    – Byron Wall
    May 12, 2015 at 15:39

1 Answer 1

Reset to default
1

It can be done by adding another column with Subtotal Function. Assumption:

1 - First Row is header row

2 - New Column added is "A" to the left of Data

3 - Filter is applied on Column "C".

A2 =SUBTOTAL(3,$C$2:C2)
A3 =SUBTOTAL(3,$C$2:C3)
...

When you apply a filter values in Column "A" will be updated with 1st visible row showing "1" in Col-A even though its actual row number may be 1000.

I have earlier used it successfully. Only limitation is that it is calculation intensive, so use with caution.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .