How do I create a dropdown selection with that selection determining other cells?

[Bailey100]

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

[#2 · Accepted solution]

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!

[#3]

@Bailey100 

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

[#4]

@nabilmourad 

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.

[#5]

@Bailey100 

Another dynamic version.  Not that the dropdown would be much fun past 100 names!

[#6]

@Sergei Baklan 

Good points ... 

Could also create a dynamic list with a defined name using an Offset Function. i do that in many situations.

[#7]

@nabilmourad 

Yes, for relatively small ranges that doesn't matter. For bigger ones OFFSET could affect the performance.