I have a sheet with events - and a column with the weekday of the event

Some days have both 3 and 4 events So, in column P, I filter out the duplicate days

enter image description here

As you can see, I have a filter on Q. If I filter the result to show only the 'X' marked rows, Tuesday will no longer be visible in row 3. If I filter by X:
- P3 will be tugged away
- P4 will be empty

Instead P4 should update and show Tuesday, while P3 is filtered out. The field is not considered hidden by Excel - but is tugged away. Therefore, a normal 'is this hidden' check fails.

Preferably this should be handled in the function in column P However a VBA or Conditional formatting solving the problem is also welcome :-)

  • 1
    What do you want to see in row 4? Right now your question is rather unclear. Please edit it further to improve it and make it more clear what you wish to see. Commented Apr 27, 2017 at 16:31
  • 1
    Perhaps including what you'd like the final data to look like would help, if you're having trouble describing it. Commented Apr 27, 2017 at 16:32
  • 1
    Possible duplicate of Excel function determine if a cell is visible Commented Apr 27, 2017 at 19:33
  • Not a dublicate - The cell is not 'hidden' - but tugged away when the filter is used - so solutions there do not apply here... Commented Apr 27, 2017 at 19:46


You must log in to answer this question.