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!

Highlighted

@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
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies