[Noob] Data Validation with dynamic, depending picklists

[cant_think_of_anythin_fun]

Hello MrExcel comunity,

hope you can maybe help me with a requirement I have. I'm fairly new to Excel and VB and english is not my first language, so i hope you can understand everything allright.

I have this Excel sheet with a lot of depending, dynamic picklists, think of it like a sort of configurator where I can configure a PC part by part. At the end of the sheet, I create a String from all the currently selected Parts in their fields and that String will be used to work further and ordering the parts.

Now to keep it simple, lets say we have five fields: Mainboard, CPU, RAM, GPU and Screen. Now when I fill them out (of course via dropdown) in that exact order, everything is fine because there can't be false combinations unless i have made mistakes in the dynamic drop downs.

Now when I selected everything in order, but went back and changed the first field, the other fields stay with the same information until i reselect that box, showing the currently selected option in the field is not even in the picklist, but it stays there untill I change it. I know this is normal and intentional and there are ways I can lock previous fields from that or clear the other fields when i jump back to another, but in my real example, I have a lot more fields than five. So clearing or locking would be a litte more work.

What I thought of but couldn't find on my own would be:
Because at the end, ultimately the String is important for the future work and everything, I don't display the code in realtime, but i place a button which does some sort of data validation before the String is even shown to the user. The user HAS to press the button before sending me the String.

So what I'm looking for is a way to validate (on execution of that button) the currently selected option in all fields against their current possible options in their dynamic picklists.
If not all selections match with their current picklist options, don't show the String at all so it can never be false.

So is there a way to make that kind of validation to make sure no field has false information when creating the String?
I hope I explained it right.

Thank you very much in advance for help.

Also if there is an even easier approach to solve this problem that you could point me in the right direction would be super too.


P.S.: Here is a link that shows clearing and locking and gereral information about depending picklists.

http://www.contextures.com/xlDataVal02.html

 

[easy2understandexcel]

How is the source data setup for the dropdowns to populate?
That is what would be used to validate so we gotta have an idea about that.

(also, i see you dont want to automatically clear all fields to the right of the changed box right?)

 

[cant_think_of_anythin_fun]

All the combinations are just written down in an (for the user) invisible sheet. Excel just checks those tables/lists to populate the drop down accordingly.
Thank you for your quick answer.

(and yes, the clear is my last resort, i don't wanna use it right away, i like the validation with button more(if that's possible))

 

[Kingarvind1]

Give a try to below :

1. Create a sheet named "List" feed in the header and data.

2. Paste below formula in validation source

=INDIRECT(LEFT(TRIM($B2),1)&"List")

 

[cant_think_of_anythin_fun]

Thank you for the reply.
So I:
1. Create a new sheet named "List" and paste all my possible combinations there (so the source data?)
2. I paste the formula in a button to make the validation?

Can you please explain in a little bit more detail? I don't know really where to paste it