Forum Discussion
How do I create a dropdown selection with that selection determining other cells?
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!
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.
Yes, for relatively small ranges that doesn't matter. For bigger ones OFFSET could affect the performance.