Dynamic data validation

**leaning** · 08-12-2011, 04:42 PM

All,

New project that I want to see if there is anything like it out there already, or anyone has any ideas on.

You have a list of characteristics (big,small,red,flower, etc.). Approximately 300 of them.
The goal is to whittle down the list so that in the end, you have a short list of characteristics that apply to a given object.
Something like this:

a) 4 item list (big, small, red, flower).
b) Dropdown in a cell: Is your object a plant? Y/N.
c) Select No, so flower comes off the list and big, small, and red are still in play.
d) Through the questions asked, whatever is left in the list is applicable to that item.

If the user changes their mind from No to Yes, flower needs to go back on the list.

Any ideas on how to set this up?

Thanks!

Lost

**tlafferty** · 08-12-2011, 04:48 PM

Sure -
named ranges and custom validation will do the trick.

Can you post a sample workbook?

**leaning** · 08-15-2011, 09:22 AM

tlafferty,

FYI, this is how this will be used. When our company gets an order for material, we have a list of codes that can be applied to that order. We have always used the same codes, even though there are other codes in the list that could be used (are still in play), but we don't apply them. With this project, all 300 codes are going to be applied to the material, and then it is up to the reviewers (based on questions asked) to knock out the codes that don't belong. I know we'll find that we have been applying 15 codes when the new list will show that there are 30-ish codes that could have been applied because nothing knocked them out.

So, if the customer says "no welding on metal", all codes relating to welding get knocked out, etc.

So, the idea is with each question to narrow the list down to any items which could be applicable unless a question can exclude them.

Real rough workbook attached. In my head, I can see it working, but translating that vision to Excel is what is stumping me.

Feel free to re-do anything in the workbook.

I appreciate your help!

Respectfully,

Lost

**tlafferty** · 08-15-2011, 03:27 PM

Hi there. I'll take a look at this a bit later today, but a forum moderator has indicated that you MUST rework your post title, so I can't post a proposed solution until you address this. How do I use dynamic data validation might be a good post title. Please take a moment to change it so I can help you.

Regards,
Tom

**leaning** · 08-15-2011, 04:30 PM

tlafferty,

Thanks!

(I changed my title earlier, but the flag is still there, so something else must be wrong. I honestly have no idea what is wrong with it. I am not using the poor thread title words, nor state a solution rather than the problem, so I am stumped.)

If I knew exactly what the offending item was, I would fix it immediately. In trying to guess what is wrong, I might make it worse.

??

Lost

**shg** · 08-15-2011, 04:34 PM

Title changed.

**tlafferty** · 08-17-2011, 12:54 PM

Attached workbook uses data validation to produce the dropdowns, each of which uses the INDIRECT function to populate the list based on the user's previous choice.

Here's a link to a quick explanation of how it works:
http://chandoo.org/wp/2010/09/13/dyn...idation-excel/

You may also want to visit the EPA.gov website and take a look at their plant taxonomy tool - it's a bit closer to what you're looking for.