extract info from an excel table to separate worksheet

Copper Contributor


I have a very long table with lots of columns and at the end is the info I want to use to form a dashboard (I've copied a very small sample below)

I want to extract (?) from this worksheet a condensed version of this onto a separate worksheet to form a dashboard without have to scroll through the table to find info.




On the separate worksheet if I select "Chainwire" from a drop down list,  I would be able to see all companies that have Yes within Column E (Timber) ie 'No name 1' 'No name 4' and No name 5'

I know that I can filter the existing table for this, but as it is very long with many columns I don't want to do it that way.

Would it be a VLookup?


3 Replies


Let's say your data are on a sheet named Data Sheet.

On the sheet with the data validation drop down, enter Company in A3, and enter the following formula in A4


=FILTER('Data Sheet'!A:A, INDEX('Data Sheet'!B:P, , MATCH(A1, 'Data Sheet'!B1:P1, 0))="Yes", "")


Adjust the ranges if your data extend beyond column P.

This will spill to as many cells as needed.




Thanks for this - unfortunately as I've got an older version of Excel the Filter function isn't available.
I'll try your solution when we upgrade our systems, soon hopefully!
Thanks !


Here is a formula that works in all versions of Excel. See attachment.