Google Sheets - How to create a dropdown filter to hide columns?

I must have been Googling and trialing/erroring in scripting for 4-5 hours now, I'm stuck in a (frustrating) loop.

I'm trying to create a drop-down filter in A1 which lists all of the values in C1:ZZZ1, and selecting one of the values hides all columns C1:ZZZ1 except the columns that fall under the selected value's merged cell.

Example from the below screenshot: In A1 would show a drop-down pulled dynamically, which lists "Joe Adams", "Eagle Nest", and "Sabrina". If I select "Eagle Nest" in the A1 drop-down, all I would see is Column A, Column B, and Columns F, G, and H (since Eagle Nest is merged with these 3 columns). The rest of the columns would be hidden. Then, upon clearing the filter (drop-down), all columns would be visible. Is something like this possible??

enter image description here

1 answer

  • answered 2018-03-13 21:50 I'-'I

    INDEX/OFFSET is another option. In another sheet, (say Sheet2!A1): Create a drop down for Sheet1!1:1,




    OFFSET(cell_reference,‎ offset_rows,‎ offset_columns,‎ [height],‎ [width])