06-25-2019 07:53 AM
06-25-2019 07:53 AM
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?
06-25-2019 08:47 AMSolution
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!
06-25-2019 09:33 AM
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.
I attached a solution for your reference
Hope that helps
06-25-2019 02:19 PM
As a comment, shifting on tables itself won't expand DV list automatically, for that
Or use dynamic range like
giving above formula referenced name.
06-25-2019 02:51 PM
Another dynamic version. Not that the dropdown would be much fun past 100 names!
06-25-2019 06:41 PM - edited 06-25-2019 06:45 PM
Good points ...
Could also create a dynamic list with a defined name using an Offset Function. i do that in many situations.
06-26-2019 09:28 AM
Yes, for relatively small ranges that doesn't matter. For bigger ones OFFSET could affect the performance.
by fjkattan on May 31, 2020
by PW0105 on May 30, 2020
by sootheng on May 30, 2020
by Ingeborg Hawighorst on May 13, 2020
by cuong on April 08, 2020