Create a template that dynamically creates column headers and associated possible values

Hi all.

I have been tasked with creating an excel template that will allow another division in our company to enter information about specific new products. This form will then be handed off to another division to process the products for retailing.

Each product can live in one of ~650 terminal categories, each category can have a varying type and number of attributes, and each attribute can have a varying set of allowable values, dependent on the category. For instance, both the "Watch" and "Cookware" categories require the "Brand" attribute, but for watches I only want to show watch brands, and the same for cookware and all other categories.

So. I know I will be using data validation, and I got really excited when I found and started working with Create dynamic (cascading) dropdowns with Data Validation | Chandoo.org - Learn Microsoft Excel Online, but my optimism is fading that this solution will fully suffice.

Each of the terminal categories (where products actually live) has a unique ID. What I would like to do is to create a spreadsheet that will take any one of those unique IDs and generate/pull in the correct attributes (column headers) and the allowable values for those attributes as data validation drop-down boxes in the rows below.

A caveat - not every attribute requires data validation. Some (like product description) require the user to enter in whatever text is specific to that product.

I do not yet have the data set that this template will be pulling from. That's coming in a few weeks, but I'd like to get ahead of the 8-ball.

A second requirement for this template is for it (eventually) to be able to reflect any changes made to attributes or values in the SQL database without requiring manual data entry. I am confident that this requirement can be met separately from the rest of the template design.

Thanks so much in advance to anyone is able to take the time to consider my requirements and help me towards a solution. Even assistance simply helping me to define my problem more precisely is very welcome.


In this link, the first sheet "template" is an example of what the end user needs to see, and sheet '12345' is an example of the data that needs to feed the template. It represents 1 of the ~650 terminal categories that the template needs to be able to pull from.


Link: https://www.dropbox.com/s/axfarvh4qg...mple.xlsx?dl=0

My apologies for the breach of etiquette. The file is attached here.

This looks like a cool project. I like fully data-driven systems. Unfortunately it will be very hard to design such a system without the source dataset that you speak of. But give me a heads-up when you have it, I'll definitely give this a bash.