Create a template that dynamically creates headers and associated possible values

[Jonathan G]

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 http://chandoo.org/wp/2014/02/13/dynamic-cascading-dropdowns-that-reset/, 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 simple helping me to define my problem more precisely is very welcome.

Jonathan

 

[NARAYANK991]

Hi ,

Two points :

1. You may not yet have the data set , but do you have a complete list of what data items will be present in the data set ; what are the fields for which data will be available later on ?

2. What will the template look like ? Is there any hand-drawn sketch / Excel worksheet which shows the format of the template ? If there is , do you have an idea of which of the fields in the template are directly drawn from the data set , and which are calculated fields which will need to be derived from the data set ?

Narayan

 

[Jonathan G]

Hi Narayan,

Many thanks for your reply. I have attached an example file. The "template" sheet is an example of what the template should have - column headers for each of the attributes, and dropdowns for the allowable values. The "12345" sheet is an example of the values that would be associated with those attributes, given the category.

If I am understanding your question correctly, I do not have a complete list of what values will be available, or even what attributes will be required. There may be certain categories that require an Attribute that no other category requires, and there are some attributes that will be required by every category.

In abstract, I'd like to be able to pull an arbitrary number and type of attributes and associated values by inputting a unique category ID.

For instance:

Category 1 requires Attributes A, B, D, W, and Z, each of which requires values 1-5.

Category 2 requires Attributes A, B, C, and Y. Attribute A requires values 1-5, but in this case Attribute B requires values 6-9. And so on.

I can kind of envision how I would do this if I created ~650 tables/sheets for each terminal category, but I can use assistance figuring out how to do that. And I'd really love it if I could find a simpler, cleaner method that would require less administrative overhead.