Create a template that dynamically creates headers and associated possible values

[seerauber]

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.

 

[mrmmickle1]

Hey, Welcome to the Message Board!

What your trying to do is definitely possible. The level of difficulty of implementing it depends on how complex your data is...which is hard to tell without an example.
It would really help out of you displayed some example data or provided a link to a non-sensitive sample file at a a file sharing website like FileSnack, DropBox, Syncplicity...etc.

It will be alot easier for someone on this board to help with your particular issue if you provide more detailed information. What you gave is a good overview but, may require more information.

 

[seerauber]

Hi mrmmickle1,

Thanks for your response. The link below is about the best I can do right now. The sheet entitled "Template" is essentially what the template will look like, though I am not wedded to any formatting in particular. Cell B2 is an example of how I'd like to be able input a unique ID to generate/pull in all of row 2. Sheet "12345" is an example of the kinds of attributes and associated values I will be dealing with. There are ~650 of these, so to speak.

As I said, certain attributes like "Brand" will be attached to every category, but will have different allowable values. Certain categories like "Describe the waistline" will be specific to only a handful, or even only one category.

Let me know if this is helpful, or if I can provide any more clarification. I am thinking right now that this is essentially going to take ~650 tables, which seems like it will be a nightmare to administrate.

Link: https://www.dropbox.com/s/axfarvh4qgbk704/example.xlsx?dl=0

 

[seerauber]

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

 

[Comfy]

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

What you're proposing sounds like one almighty job.

But!!! I think I use something that might be able to assist you. I will PM you tonight once I get home.

I work in finance and we use an Excel application to create csv files which are imported into our Finance System (this is so that we can process thousands of rows quickly)

One of the issues with this is that there is no Validation in the Excel Application and so I have added the Conditional Formatting to help, you could take this one step further using VBA to add data validation.

For Example:

In finance we group Income and Expenditure. (E.g. code 12345 could be for Training)

Each code can have anywhere between 1 and 7 categories associated with it, some madatory, some optional.

So if we use 12345 we would also have to record:

Category 1 - Budget Code
Category 2 - Staff ID number.

These are called "Rules", let's call this Rule 1.

Now imaging a code for Staff Travel, 98765.

This would also be linked to Rule 1 as we wish to record the same info.

And so on......

I think your first task is to group your Categories and give them a unique Rule (or what ever you wish to call this).


.........

But depending on the complexity it might be better if you build this in Access.

 

[seerauber]

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

Thanks for the reply Comfy. I will consider how what you have suggested can be applied to my problem, and look forward to your PM in the meantime. You have my gratitude for your time!

 

[RoryA]

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

Threads merged. Please do not repost the same question.