PDA

View Full Version : Solved: 6 dependant drop down boxes



XL^
05-09-2009, 03:36 AM
Hi there,
I have a sheet with 6 columns and x number of rows, each row makes up a unique sequence (Sequences are horizontal).
Some of the cells are the same as other rows but the end result is always unique.
What im trying to do is use dependant drop down boxes on another sheet to act as validation.
The idea is you use the 6 dependant drop down boxes, so that the end result is one of the unique sequences from the other sheet.
Ive been down the route of named ranges but it doesnt seem to cater for what I am attempting to do.
Im capable with both excel and vba but not sure how to address this.
Thanks

Bob Phillips
05-09-2009, 04:36 AM
See http://www.contextures.com/xlDataVal02.html

XL^
05-09-2009, 04:43 AM
Thanks for the quick reply.
Ive already tried this approach (using that very site) but it doesnt work because the 2nd etc columns are not lists as such. In my case it would be like each cell on each row of each column would need to be a seperate list (named range) which just wouldnt be feasible?

mdmackillop
05-09-2009, 12:29 PM
I can't see what the end result should be. Can you post a sample workbook with expected outcome?

Bob Phillips
05-09-2009, 02:10 PM
Maybe this will give you a steer

XL^
05-11-2009, 05:02 AM
I have attached a sample spreadsheet with a few notes to explain my self a bit better.

Thank you

mdmackillop
05-11-2009, 06:58 AM
Does Fedex give rise to 3 options in Column C, or do you combine A & B to give Asda/Tesco drivers. Similarly for Product/vehicle and Destination columns.
Your example is a bit sparse on detail, and there are many ways to waste time getting it wrong.

XL^
05-11-2009, 07:05 AM
Bascially the 7 rows are unqiue sequences.
The idea is it will only let you pick from the 6 dropdown boxes so that it matches ones of those sequences.

You should always pick the pick from the first column first, say you pick Sainsburys, the 2nd drop down box should only show Van4fire and Delivery2u. If you picked Van4hire it should only show John and Fred since they are the only possible in the options in one of the sequences listed. From there if you picked John, you would now only see pencils then Fiesta then France because this would be the only possible sequence it could match.

Hope this helps to explain further?

mdmackillop
05-11-2009, 07:53 AM
OK I follow that.

GlazedIZ
05-11-2009, 12:42 PM
If it helps steer you into a solution, what you are talking about is called Cascading ComboBoxes. I was working on something similar a few months ago without success, hope you find your solution.

mdmackillop
05-11-2009, 02:38 PM
try this.

XL^
05-12-2009, 02:21 AM
Thank you mdmackillop that worked exactly as I was required!
And thanks to everyone else who contributed.

Would it be possible to do a quick run through each sub just to fully understand your method please?

Thanks again!

mdmackillop
05-12-2009, 04:03 AM
The code runs repeated Advanced Filters. Unhide the hidden columns on the Data sheet. Set up your window to show both sheets
Step through the code and you can see the actions.