Return data from a specific column based on what's selected from a drop down list

[Iain Garey]

Hi All

I have an excel sheet with 70 columns with each one having a header if the relevant area, there are also 80+ rows under each header.

Rather than having to split these 70 columns and rows on to separate tabs I would like to, on a separate tab, be able to select one of the header names from a Data Validation list already created, which would result in the information held under that specific header for all the rows being shown.

Many thanks

Iain

[José Augusto]

Hi
Consider your data on Sheet1!A1:Z100 and row 1 has headers.
Considere Sheet2!A1 as the value of the header you want.

Try in Sheet2!B1 and copy down

Please Login or Register  to view this content.

(not tested)

[Iain Garey]

Hi

I've tried this, although where you've entered ; within the formula I've replaced with , as it was copying it as text. Is this correct?

Having done this I get the answer of #NAME? instead of the expected answer.

I have attached a copy of a version of this sheet, with two tabs, Sheet1 and Sheet2 if that helps.

regards

Iain

[FlameRetired]

Try changing formula to:

Please Login or Register  to view this content.

That fixes the #NAME? error. LINS didn't make it through the translator.

[Iain Garey]

Thanks Dave

I've updated this and am now getting an answer of Aberdeen, however was expecting the answer of 1, which is the value showing in cell A2 of Sheet1.

Regards

Iain

[FlameRetired]

Try this in Sheet2 A2 and fill down.

Please Login or Register  to view this content.

Needs to MATCH the column header.

[FlameRetired]

Actually this is simpler.

Please Login or Register  to view this content.

[Iain Garey]

Thank you sir, both these work a treat

[FlameRetired]

You are welcome. Thank you for the feedback, added rep and marking your thread Solved.