Forum Discussion
How do I create a dropdown selection with that selection determining other cells?
Hi,
I'm creating a document, see attached, where i want to have a selection of clients via a drop down and whatever client i choose (maybe out of a list of 6) this in turn will automatically fill out the contact name, mobile, telephone based on who the client is. (i.e if i pick harrys as a client Ann would appear next to contact, 123 next to mobile... etc)
How would I do this?
Thanks,
Bailey100
Hello Bailey100,
To create a drop-down list go under the Data tab and select data validation. Under "Allow:" select "list" and change your source to the source of your list (i.e. the client names).
Let's assume you put your drop-down list in cell A1 and named the client information table "Client_Info". Then in the cell to the right of "Contact:", you will put the formula =VLOOKUP($A$1,Client_Info,2,FALSE). Next to "Tel:" would be =VLOOKUP($A$1,Client_Info,4,FALSE). Next to "Mob:" would be =VLOOKUP($A$1,Client_Info,3,FALSE). Next to "Email:" would be =VLOOKUP($A$1,Client_Info,5,FALSE).
Now just select a name from the drop-down and watch your list autofill.
Hope this helps!
6 Replies
Another dynamic version. Not that the dropdown would be much fun past 100 names!
Creating a drop down lit is easy (Data Tab >> Data Validation >> Allow >>List >> Select the values)
Alternatively you can use the Shortcut ALT +D +L
I have a preference to converting your source list into a Table (Becomes expandable) using the shortcut CTRL + T , you can then Name it "Source"
For the VLOOKUP that extracts a record from the source you do not need to create multiple versions of the VLOOKUP, just one and replace the hard coded number with a ROWS function that increments automatically as you drag down.
=VLOOKUP($B$1,Source[#All],ROWS($I$1:I2),0)
I attached a solution for your reference
Hope that helps
Nabil Mourad
As a comment, shifting on tables itself won't expand DV list automatically, for that
=INDIRECT("Source[Names]")Or use dynamic range like
=data!$A2:INDEX(data!$A:$A,COUNTA(data!$A:$A))
giving above formula referenced name.
Good points ...
Could also create a dynamic list with a defined name using an Offset Function. i do that in many situations.
Hello Bailey100,
To create a drop-down list go under the Data tab and select data validation. Under "Allow:" select "list" and change your source to the source of your list (i.e. the client names).
Let's assume you put your drop-down list in cell A1 and named the client information table "Client_Info". Then in the cell to the right of "Contact:", you will put the formula =VLOOKUP($A$1,Client_Info,2,FALSE). Next to "Tel:" would be =VLOOKUP($A$1,Client_Info,4,FALSE). Next to "Mob:" would be =VLOOKUP($A$1,Client_Info,3,FALSE). Next to "Email:" would be =VLOOKUP($A$1,Client_Info,5,FALSE).
Now just select a name from the drop-down and watch your list autofill.
Hope this helps!
