Match dynamic value in another sheet and return values in that column as a drop down list

[Omer104]

Hi everyone,

I wanted to check if its possible to have an excel formula

I have a dynamic value in sheet1 that looks to match with data in row 1 (in another sheet2).

So for example USA is the value and the other sheet has in row 1 has USA in cell A1, GERMANY in cell B1, FRANCE in cell C1, etc and the list goes on for many countries.

Once it has found the specific column (in this case column 1.)

The values below USA in column1 are available as a drop down list to select.

So for example the values under USA could be red (in cell A2), blue (in cell A3) and green (in cell A4), these values could be different for the other countries .

Many thanks

 

[duggie33]

Please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using, as the best solution often varies by version. Don’t forget to scroll down to save your changes.

Please provide some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. As of October 2023, the latest version is 2.1. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks,

Doug

 

[Omer104]

sorry no problem its excel 365.

in Cell A1 is USA - sheet1, I want to match in another sheet2 this value in this case USA with Row 1:1 data,

In this case it is COLUMN A, and the drop down list should show red, blue


USA, FRANCE, GERMANY , etc , etc etc
red green black
blue yellow white
grey purple

 

[duggie33]

Here is an example of one way to do this...

Book2.xlsx
	A	B
1	USA	blue
2
3
4
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=lstCountries
B1	List	=lstCountryData

Book2.xlsx
	A	B	C
1	USA	FRANCE	GERMANY
2	red	green	black
3	blue	yellow	white
4	gray	purple
Sheet2

I am not sure why it is not showing the named range information...
lstCountries =

=Sheet2!$A$1:$C$1

lstCountryData =

=OFFSET(Sheet2!$A$1,1,MATCH(Sheet1!$A$1,lstCountries,0)-1,20,1)

You will have to change the ranges and or formulas to match your data or make it more dynamic.

I hope that helps,

Doug