SOLVED

Add a cell with text according to a list of choices - VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-2583857%22%20slang%3D%22en-US%22%3EAdd%20a%20cell%20with%20text%20according%20to%20a%20list%20of%20choices%20-%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2583857%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20don't%20know%20where%20to%20start%20this%20kind%20of%20macro.%20But%20I%20have%203%20lists%20that%20if%20I%20selected%20one%20from%20the%20lists%2C%20it%20will%20then%20populate%20certain%20text%20in%20a%20cell.%20Say%20for%20example%20on%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22rbalza_0-1627183399497.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F298205iC4D99BE3D1A62098%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22rbalza_0-1627183399497.png%22%20alt%3D%22rbalza_0-1627183399497.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2583857%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2585314%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20cell%20with%20text%20according%20to%20a%20list%20of%20choices%20-%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2585314%22%20slang%3D%22en-US%22%3EIs%20it%20a%20complete%20file%3F%20Where%20are%20the%20ingredients%20lists%20which%20you%20want%20to%20populate%20based%20on%20the%20selection%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2585316%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20cell%20with%20text%20according%20to%20a%20list%20of%20choices%20-%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2585316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098170%22%20target%3D%22_blank%22%3E%40rbalza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20wouldn't%20have%20to%20involve%20macros%20or%20VBA%20routines.%20This%20can%20be%20done%20with%20what%20I've%20called%20a%20cascading%20set%20of%20Data%20Validation%20steps.%20I've%20attached%20an%20example%20that%20works%20with%20first%20and%20last%20names%2C%20but%20the%20same%20logic%20could%20apply%20to%20sets%20of%20ingredients%20based%20on%20a%20first%20selection%20of%20the%20dish%20to%20prepare.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%3A%20these%20functions%20do%20require%20the%20most%20recent%20version%20of%20Excel.%20Here's%20a%20YouTube%20video%20that%20explains%20the%20Dynamic%20Array%20functions.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D9I9DtFOVPIg%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2585322%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20cell%20with%20text%20according%20to%20a%20list%20of%20choices%20-%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2585322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3Bplease%20find%20the%20attached%20file.%20Thanks%20in%20advance%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2585325%22%20slang%3D%22en-US%22%3ERe%3A%20Add%20a%20cell%20with%20text%20according%20to%20a%20list%20of%20choices%20-%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2585325%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Bthanks%20for%20taking%20the%20time%20on%20this.%20will%20look%20on%20the%20link%2Fsheet%20that%20you%20have%20provided.%20Cheers!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi everyone, don't know where to start this kind of macro. But I have 3 lists that if I selected one from the lists, it will then populate certain text in a cell. Say for example on the attached file. 

 

rbalza_0-1627183399497.png

 

7 Replies
Is it a complete file? Where are the ingredients lists which you want to populate based on the selection?

@rbalza 

 

It wouldn't have to involve macros or VBA routines. This can be done with what I've called a cascading set of Data Validation steps. I've attached an example that works with first and last names, but the same logic could apply to sets of ingredients based on a first selection of the dish to prepare.

 

Note: these functions do require the most recent version of Excel. Here's a YouTube video that explains the Dynamic Array functions. https://www.youtube.com/watch?v=9I9DtFOVPIg

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...

@Subodh_Tiwari_sktneer please find the attached file. Thanks in advance

@mathetes thanks for taking the time on this. will look on the link/sheet that you have provided. Cheers!

@rbalza 

Please find a simple setup with three named ranges called "Apple_Pie", "Custard_Pie" and "All_Pies" on the List Sheet then you may select an item from the dropdown in B1 to populate the list of corresponding ingredients. See if this is what you were trying to achieve.

The Code is placed on Sheet1 Module for Sheet_Change Event.

 

 

 

 

@Subodh_Tiwari_sktneer 

 

Hi, appreciate the time you are taking out on this. Is there a way to make it like a clickable cell rather than a drop-down list?

best response confirmed by rbalza (Contributor)
Solution

@rbalza 

Okay, please find the attached and once you select any cell in the range B1:D1, the corresponding ingredients list will be populated.