4 way dependent dropdown/data validation

**Cidona** · 02-17-2012, 02:36 PM

Dear Form.

Not sure if what I'm looking for is posible:
I found some articles regarding dependent dropdowns/datavalidation; such as here http://www.contextures.com/xldataval02.html
However I'm wondering if it posible to do so while without having to separate the catogories. Reason I don't want to create separate lists for each of the dropdown lists is I'm am looking to use with with a worksheet that has approx 5,000 rows which is imported from time to time from an access database with new items. Would like it if I didn't have to separate items when this is being done. (Maybe the only way?)
I've attached a sample workbook. In this example there is a worksheet called 'Database' which contains the data. There is another worksheet call 'Pricer', which is intended for the user to price up jobs. On the 'Pricer' worksheet I have filled out the first two row of the pricer (rows 5 and 6). I'm wondering if there is a way have dependent dropdowns by looking directly at the 'Database' worksheet, i.e. without creating lists for each catogory combination.

Any advise would be appreciated.

**dilipandey** · 02-19-2012, 03:31 AM

Hi Cidona,

I have seen in the attachment that you have used the database directly for drop down list and did not created the separate lists .. and this is what you are asking here.. Only thing I see here as an improvement that whenever the database rows increases /decreases, your drop down list should automatically pick up that change.. in order to do this, you can create dynamic names using offset function..

Let me know if I an on right track. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**Marcol** · 02-19-2012, 06:58 AM

Your posted sample doesn't lend itself to your query. Why not post a sample of your actual data?

However you basically need a unique list to base the solution on, I have chosen Column "Manufacturer" for this example.
The Data Table should be Sorted/Grouped on the chosen column.

See the defined names in the Names Manager to see how the dynamic names are built.

Select from the drop-down in sheet "Pricer" Column "Manufacturer", then from the drop-down in "Application Method"

This might give you a start, it won't in itself solve your problem.

With a database of some 5000 rows I would avoid using the volatile function OFFSET() when defining names, use INDEX() & MATCH() instead.