Forum Discussion
extract info from an excel table to separate worksheet
Hello
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 !
AprilHere is a formula that works in all versions of Excel. See attachment.
