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:

Category	Division	Lookup	Directorate
Admin	Finance	AdminFinance	800.555.1234
Admin	Operations	AdminOperations	800.555.2345
Support	Tech	SupportTech	800.555.3456
Support	People	SupportPeople	800.555.4567

<tbody>
</tbody>

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:

Category	Division	Directorate

<tbody>
</tbody>

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