Displaying data horizontally after a table filter

[martinpgibson]

Hi there I am trying to display data from a table on another sheet horizontally. If you look at the attachment and on the sheet called "Data" there is a table, and on the sheet called "Sheet1" it is pulling names from that table and displaying them horizontally. However the problem is that when I apply a filter to that table on the "Data" sheet, let's say animals that are white in colour, the headings on the first page stay the same. Whereas what I really want is for the names on the first page to show (if the filter was white) a cockatoo and a polar bear in the first 2 heading fields and the remaining ones to be blank.

I totally get why it's not working for me because when you apply a filter it is essentially just hiding rows and the references on "Sheet1" are to direct cells. I have tried all sorts of structured references but I can't figure out a way to do it. If you can help me that would be much much appreciated!

animal problem.xlsx

[:) Sixthsense :)]

Check whether the method used in the attached files is helpful...

[martinpgibson]

Hi Sixthsense,

This is awesome how you did that, but is is possible to have it so that the items at the top of the table show up on the left hand side of the headings. Like at the moment if you do a filter it just hides the item from the heading but doesn't shift/align them to the left? Cheers,

[benishiryo]

hi martinpgibson, try this array formula maybe:

Please Login or Register  to view this content.

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[martinpgibson]

Hi benishiryo, unfortunately that doesn't work. DO you have any idea why that might be? I am familiar with array formulas. If you are able to get it working could you maybe attach it to this thread?

Thanks!