SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-719753%22%20slang%3D%22en-US%22%3EHow%20do%20I%20create%20a%20dropdown%20selection%20with%20that%20selection%20determining%20other%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719753%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20creating%20a%20document%2C%20see%20attached%2C%20where%20i%20want%20to%20have%20a%20selection%20of%20clients%20via%20a%20drop%20down%20and%20whatever%20client%20i%20choose%20(maybe%20out%20of%20a%20list%20of%206)%20this%20in%20turn%20will%20automatically%20fill%20out%20the%20contact%20name%2C%20mobile%2C%20telephone%20based%20on%20who%20the%20client%20is.%20(i.e%20if%20i%20pick%20harrys%20as%20a%20client%20Ann%20would%20appear%20next%20to%20contact%2C%20123%20next%20to%20mobile...%20etc)%3C%2FP%3E%3CP%3EHow%20would%20I%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBailey100%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-719753%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719879%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20dropdown%20selection%20with%20that%20selection%20determining%20other%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719879%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20create%20a%20drop-down%20list%20go%20under%20the%20Data%20tab%20and%20select%20data%20validation.%20Under%20%22Allow%3A%22%20select%20%22list%22%20and%20change%20your%20source%20to%20the%20source%20of%20your%20list%20(i.e.%20the%20client%20names).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20assume%20you%20put%20your%20drop-down%20list%20in%20cell%20A1%20and%20named%20the%20client%20information%20table%20%22Client_Info%22.%20Then%20in%20the%20cell%20to%20the%20right%20of%20%22Contact%3A%22%2C%20you%20will%20put%20the%20formula%26nbsp%3B%3DVLOOKUP(%24A%241%2CClient_Info%2C2%2CFALSE).%20Next%20to%20%22Tel%3A%22%20would%20be%26nbsp%3B%3DVLOOKUP(%24A%241%2CClient_Info%2C4%2CFALSE).%20Next%20to%20%22Mob%3A%22%20would%20be%26nbsp%3B%3DVLOOKUP(%24A%241%2CClient_Info%2C3%2CFALSE).%20Next%20to%20%22Email%3A%22%20would%20be%26nbsp%3B%3DVLOOKUP(%24A%241%2CClient_Info%2C5%2CFALSE).%3C%2FP%3E%3CP%3ENow%20just%20select%20a%20name%20from%20the%20drop-down%20and%20watch%20your%20list%20autofill.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-719911%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20dropdown%20selection%20with%20that%20selection%20determining%20other%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719911%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECreating%20a%20drop%20down%20lit%20is%20easy%20(Data%20Tab%20%26gt%3B%26gt%3B%20Data%20Validation%20%26gt%3B%26gt%3B%20Allow%20%26gt%3B%26gt%3BList%20%26gt%3B%26gt%3B%20Select%20the%20values)%3C%2FP%3E%3CP%3EAlternatively%20you%20can%20use%20the%20Shortcut%20ALT%20%2BD%20%2BL%3C%2FP%3E%3CP%3EI%20have%20a%20preference%20to%20converting%20your%20source%20list%20into%20a%20Table%20(Becomes%20expandable)%20using%20the%20shortcut%20CTRL%20%2B%20T%20%2C%20you%20can%20then%20Name%20it%20%22Source%22%3C%2FP%3E%3CP%3EFor%20the%20VLOOKUP%20that%20extracts%20a%20record%20from%20the%20source%20you%20do%20not%20need%20to%20create%20multiple%20versions%20of%20the%20VLOOKUP%2C%20just%20one%20and%20replace%20the%20hard%20coded%20number%20with%20a%20ROWS%20function%20that%20increments%20automatically%20as%20you%20drag%20down.%3C%2FP%3E%3CP%3E%3DVLOOKUP(%24B%241%2CSource%5B%23All%5D%2CROWS(%24I%241%3AI2)%2C0)%3C%2FP%3E%3CP%3EI%20attached%20a%20solution%20for%20your%20reference%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-720277%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20dropdown%20selection%20with%20that%20selection%20determining%20other%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-720277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20comment%2C%20shifting%20on%20tables%20itself%20won't%20expand%20DV%20list%20automatically%2C%20for%20that%3C%2FP%3E%0A%3CPRE%3E%3DINDIRECT(%22Source%5BNames%5D%22)%3C%2FPRE%3E%0A%3CP%3EOr%20use%20dynamic%20range%20like%3C%2FP%3E%0A%3CPRE%3E%3Ddata!%24A2%3AINDEX(data!%24A%3A%24A%2CCOUNTA(data!%24A%3A%24A))%3C%2FPRE%3E%0A%3CP%3Egiving%20above%20formula%20referenced%20name.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-720336%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20dropdown%20selection%20with%20that%20selection%20determining%20other%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-720336%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F351100%22%20target%3D%22_blank%22%3E%40Bailey100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20dynamic%20version.%26nbsp%3B%20Not%20that%20the%20dropdown%20would%20be%20much%20fun%20past%20100%20names!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-720649%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20dropdown%20selection%20with%20that%20selection%20determining%20other%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-720649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20points%20...%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20also%20create%20a%20dynamic%20list%20with%20a%20defined%20name%20using%20an%20Offset%20Function.%20i%20do%20that%20in%20many%20situations.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-722308%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20dropdown%20selection%20with%20that%20selection%20determining%20other%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-722308%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20for%20relatively%20small%20ranges%20that%20doesn't%20matter.%20For%20bigger%20ones%20OFFSET%20could%20affect%20the%20performance.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bailey100
Contributor

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

6 Replies
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!

@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

@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.

@Bailey100 

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

@Sergei Baklan 

Good points ... 

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

@nabilmourad 

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

Related Conversations