Column data selection drop-down

[CaptainGravyBum]

Hello,
Hoping this is an easy to answer, not easy to explain.
I have two spreadsheets, sheet1 has headers but no data and sheet2 holds just data. The headers are different on each sheet and I need to get sheet1 populated based on the columns in sheet2 without having to go back and forth between them.
Is there a way to have a drop-down list under each heading in sheet1 where I can match the required column in sheet2 and import the required rows when the column has been selected?
A bit like database matching in access.
For example Sheet1 ColumnA is Date which matches Column2 on Sheet2 and will populated the rows with data in them from sheet2 into sheet1.

So the drop down on sheet1 will show the headers or column numbers from sheet2 and when a selection is made the rows will auto fill with data.

Hope that makes some sense.

 

[SanjayGMusafir]

Hope that makes some sense

Kind of confusing...

But from what I have sensed You want to make a drop down list of headers...

After that you can use Filter Function on your data in Sheet 2 to pull it on Sheet 1. But there has to be significant correlation between the two. Else you have establish that using IF or IFS function for excel to know the corelation.

Sheet 2 having data without headers make me think... Still Filter Function can be applied.

 

[CaptainGravyBum]

Kind of confusing...

But from what I have sensed You want to make a drop down list of headers...

After that you can use Filter Function on your data in Sheet 2 to pull it on Sheet 1. But there has to be significant correlation between the two. Else you have establish that using IF or IFS function for excel to know the corelation.

Sheet 2 having data without headers make me think... Still Filter Function can be applied.

Hi @SanjayGMusafir,
I'm not sure this will achieve my desired result. I would like the cells in sheet1 to have a drop down menu that shows all of the column headers from sheet2. When one of the headers is chosen, the data from the selected header name will be imported from sheet2 into the selected column in sheet1.

So if I'm in sheet1 cell A2 and pick the date column from a header list using sheet2's data, the whole date column from sheet2 will import into column A of sheet1.

 

[SanjayGMusafir]

So if I'm in sheet1 cell A2 and pick the date column from a header list using sheet2's data, the whole date column from sheet2 will import into column A of sheet1.

I think that is much easier than I originally thought. Check this and revert - Here Cell A1 is drop down which you might not see in XL2BB. C2:C7, D2:D7, E2:E7 are named ranges

Book1
	A	B	C	D	E
1	Test1		Test1	Test2	Test3
2	Data 01 01		Data 01 01	Data 02 01	Data 03 01
3	Data 01 02		Data 01 02	Data 02 02	Data 03 02
4	Data 01 03		Data 01 03	Data 02 03	Data 03 03
5	Data 01 04		Data 01 04	Data 02 04	Data 03 04
6	Data 01 05		Data 01 05	Data 02 05	Data 03 05
7	Data 01 06		Data 01 06	Data 02 06	Data 03 06
Sheet1

Cell Formulas
Range	Formula
A2:A7	A2	=INDIRECT(A1)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1	List	Test1,Test2,Test3

 

[CaptainGravyBum]

I think that is much easier than I originally thought. Check this and revert - Here Cell A1 is drop down which you might not see in XL2BB. C2:C7, D2:D7, E2:E7 are named ranges

I like this idea, but sheet2 has dynamic data in the cells so i can't name the range as such and a bigger issue is there are 39 columns in the data sheet which would be difficult to name or select from a list.

 

[SanjayGMusafir]

I like this idea, but sheet2 has dynamic data in the cells so i can't name the range as such and a bigger issue is there are 39 columns in the data sheet which would be difficult to name or select from a list.

You can always make dynamic data a Named Range and 39 columns if you need it...

Anyhow, that was the easiest and workable solution I could provide...