Macro to choose from Dynamic Lists and then populate cells

[jonniew]

Hi,

I'm looking to create a macro that will allow me to setup a spreadsheet template whereby the user is prompted to select one value, then another and on the basis of these 2 other values are populated. So for example, the user is prompted to choose a category and then a division, which then in turn populates a directorate and location. I've done much searching both on here and on google but have not managed to find anything. Hoping someone be able to assist, I unfortunately have a fairly limited knowledge of macros, the little knowledge I do have having come from my old friend, Mr Google.

Any assistance, even if you can point me in the right direction of a resource that might help me would be greatly appreciated.

Thanks,

Jonnie

 

[john316swan]

I would tackle this by first creating dynamic named range for each lookup, then a table that has all of your admin & corresponding divisions. Then concatenate all category & divisions to lookup the directorate like so:

[TABLE="class: grid, width: 361"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Division[/TD]
[TD]Lookup[/TD]
[TD]Directorate[/TD]
[/TR]
[TR]
[TD]Admin[/TD]
[TD]Finance[/TD]
[TD]AdminFinance[/TD]
[TD]800.555.1234[/TD]
[/TR]
[TR]
[TD]Admin[/TD]
[TD]Operations[/TD]
[TD]AdminOperations[/TD]
[TD]800.555.2345[/TD]
[/TR]
[TR]
[TD]Support[/TD]
[TD]Tech[/TD]
[TD]SupportTech[/TD]
[TD]800.555.3456[/TD]
[/TR]
[TR]
[TD]Support[/TD]
[TD]People[/TD]
[TD]SupportPeople[/TD]
[TD]800.555.4567[/TD]
[/TR]
</tbody>[/TABLE]

Then you can use data validation for your users to lookup the category, then the division and then do a vlookup for the directorate. No macro's required

So if your input table looks like this:
[TABLE="class: grid, width: 242"]
<tbody>[TR]
[TD]Category[/TD]
[TD]Division[/TD]
[TD]Directorate[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Once a user inputs a category and division, you would use vlookup like =IFERROR(VLOOKUP(A2&B2,LookupTable,2,FALSE),"Directory Not Found")