Dynamically generated data validation question

**francisco** · 08-04-2012, 03:16 PM

hi fellows!
I am working on a spreadsheet for Order Entry that contains lots of VLOOKUP functions and uses
separate sheets that contains customer info, sales info (sales person name, terms, etc), and of course the
style or product information.
The spread sheet was completed but someone decided to ask for a feature to make their live easy (I don't blame them!).
The feature was to have excel automatically fill the cell with available style from the "Style" sheet while the style was being type.
The user would type or select from the Drop down list and the entire row will populate with retail and sales price, and shade the sizes
NOT available (so the user can enter the quantity only in sizes available)
As you may imagine, there are several colors available for one style. From the drop down list, you can see the styles and some will be repeated
as each one represent the combination of style/color. This could probably be fixed but I don't know how.
The problem now is that after the auto fill feature, the color seems to be always the first color listed for the style selected. This was not a problem when I had just the Data validation in the Style column.
I have though of several other solutions but it seems I hit a death.
Any help will be greatly appreciated.
I am including the form as an attachment.

**mrice** · 08-04-2012, 03:33 PM

I'm guessing that you would want to select the style first and then have a drop down in the colour column which contained just the colours appropriate for that style. Once both values are populated, then other fields could be looked up. Is this correct?

If so, you are probably looking at an event driven macro to produce a dynamically generated data validation.

**Cutter** · 08-04-2012, 04:36 PM

Hello francisco, and welcome to the forum.

Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**francisco** · 08-04-2012, 06:15 PM

Cutter, Sorry for broken the first rule. My reasoning is based on the fact that there could be multiple functions that could solve the problem. Never the less, I change the title. I hope it makes sense now.

Mrice: You are correct! It will be nice to select the color from the COLOR column based on colors available for the selected style.
As for populate the rest of the row until color is selected, yes, It will be correct too. Although the style determine that information not the color.
That would be more on user experience dealing with the form.
I would populate the row with the style information once the style is selected and the select the color. But again, which option would be easier?
Do I make sense?
Thank you