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.

Community
  • 1
SilverWarior
  • 156
  • 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 '15 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 '15 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 https://support.office.com/en-in/article/SUBTOTAL-function-bbecc044-0889-4185-9685-3d2c8b72826f – Byron Wall May 12 '15 at 15:39

1 Answers1

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.

Shivam7898
  • 61