CODEWITHSUNDEEP
excelvalidationdrop-down-menu
evenmonkeys
evenmonkeys

Reputation: 13

Excel Drop-Down List to Take from Multiple Columns

I have been trying to figure this out for two hours now and I just need to ask for help. I am trying to have a drop down list in Excel that when you select an item, it will pull info from the columns next to it as well.

SHEET 1

Name | City | State
John | Cty1 | CA
Luke | Cty2 | IA
Kris | Cty3 | TX

SHEET 2

Name | City | State
___v | ____ | __

So what I am trying to do is when you select the name on SHEET 2, it will take the City and State from SHEET 1 and auto populate it into the row on SHEET 2. Can someone please help me with this? I am going nuts.

Thank you.

Upvotes: 1

Views: 3098

Answers (1)

India.Rocket
India.Rocket

Reputation: 1245

Try This:-

To create dropdown for Name

1. First copy paste values of NAme from Sheet1 in Sheet2. Say you pasted them in A1 to A3.
2. Under name (say D3 cell in sheet2) column go to D4 cell just below name,then Go the 'Data validation' tab under 'data' tab. Select 'data validation' again, a dialog box will appear.
3. In the dialog box under 'Allow' select 'List' and then in 'Source' make a selection from A1 to A3.


    Now Use this formula in sheet2 
    for city 
    =IFERROR(VLOOKUP(D4,Sheet1!$D$15:$F$18,2,0)," ")

    for state
    =IFERROR(VLOOKUP(D4,Sheet1!$D$15:$F$18,3,0)," ")

    This is assuming in Sheet1 D15 to D18 has values for column Name & D4 is value of Name or Name dropdown in Sheet2

Hope this helps:-)

Upvotes: 1

Related Questions