[Noob] Data Validation with dynamic, depending picklists

Oct 4, 2016
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.


Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.


Active Member
Dec 26, 2012
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?)
Last edited:
Oct 4, 2016
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))


New Member
Nov 26, 2009
Give a try to below :

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

2. Paste below formula in validation source


Oct 4, 2016
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
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back