Home > Mobile >  Google Sheets: EXCLUDE hidden rows in query pulling unique data using indirect function
Google Sheets: EXCLUDE hidden rows in query pulling unique data using indirect function

Time:03-09

I have an Apps Script pulling every tab name in my Google Sheet into a list. I'm then using a Query to pull all unique Names (from a Name column) from each of those tabs, with the Indirect function.

There are currently hidden rows in each of the tabs (there are 15 tabs total). The Query I have is currently pulling the Names in those hidden tabs.

How do I tell the Query to pull all unique data EXCEPT the hidden rows? Here is the existing Query formula I have which is including hidden row data (I'm only including 2 tabs of data here to shorten the formula for this example):

=UNIQUE(QUERY({IF(B2="", {"","","",""}, INDIRECT(B2));IF(B3="", {"","","",""}, INDIRECT(B3))},"Select Col4 where Col4 is not null order by Col4 label Col4 'Names'",1))

The {"","","",""} is because I have INDIRECT formulas for future anticipated tabs which don't yet exist in the list of tabs, so I won't have to update the formula every time a new tab is added. For excluding the hidden data, I've looked into the SUBTOTAL formula but I'm having trouble applying it to this situation.

enter image description here

  • Related