Display entire column data in another sheet depending upon a filter selected

[Dianxue]

Hi everyone,
i've been stuck with a problem for the past few hours and hopefully you can help me as I couldnt really find a solution on the web.

I have 2 sheets constructed the following:
- Sheet A with plenty of data classified by <field names> as row titles and <Functionality names> as collumns in which plenty of values have been entered (couple of 100 cells)
- Sheet B where I want to display the values of <Functionalities> depending upon a filter selection and use the values displayed there to create automated test scripts.

My problem is that i cant get the values to be displayed properly in the second sheet.

What i intend to do in Sheet B:

- 1 Cell in which I have a filter to select different <Functionalities> (done), depending on the values used in Sheet A (basically collumn headers).
- Depending on that filter all values of that selected <Functionality> should be displayed.

--> In short i want to have something like a horizontal filter without the other collumns being present which is displaying the data from another sheet.
Is there an elegant formula to do this ? The lookup formulas are not really elegant.

--------------------
Further information:
- 1 collumn consists of approx. 400 cells which may have the values x, y, m, s or be empty.
- Each collumn has a unique identifier
- in the second sheet I want to be able to select the <identifier> from a predefined list (which i have created already) and then have all the values displayed below in the same manner as they are in the first sheet.

This data will then be part of further functions hence I want to split it accross multiple sheets as the list of arguments will become too long if i try to do this in one sheet.

Any input / help is really appreciated as I have been stuck with this the past few hours and cant really progress further.

thank you all !

[NBVC]

Try:

=INDEX('Sheet A'!$A:$Z,ROWS($A$1:$A1),MATCH($A$1,'Sheet A'!$A$1:$Z$1,0))

copied down same number of rows as the Sheet A database.

where it is assumed your Sheet A database covers columns A:Z and A1 on your SheetB contains the Data Validation you choose from.

[Dianxue]

thank you a lot!
i think in principal your formula is working. I just need to get now the collumn / row names working properly but in a dummy trial I could get it to work!

awesome saved me (and my client:P) a lot of time and money

... i was always thinking along the lines of hlookup but couldnt seem to get it straight....

[NBVC]

=HLOOKUP($A$1,'Sheet A'!$A$:$Z$,ROWS($A$1:$A1),0) copied down would also work, I think....