ansaurus

Question

xls cascading lookup based on pivot table style datasource?

Answer 1

A:

Select the range then Data -> Filter -> Autofilter. Does that achieve what you're looking for?

Alistair Knock 2009-03-24 11:04:20

No because I to have the solution available on numerous rows, so that the user can drill down through the data for one row, then move on to the next

adolf garlic 2009-03-24 11:09:57

Answer 2

A:

I think you want to use the INDIRECT function to lookup the value of a cell, rather than define a lookup range directly in the data validation. Have a look at this page which explains it fairly well.

Be warned though that multiple cascading validations like this have particular problems of their own. Chiefly, if you make a second (or third etc.) selection, then go back and edit the first selection, there is no automatic flag to say that the selection may now be invalid. For example, if you have the following:

Type        Detail
Fruit       Apple

...and then change the first column:

Type        Detail
Vegetable   Apple

...then you may now have a problem. Finding such inconsistencies can be very hard, or requires some code to highlight/fix. The DDoE blog shows one method to highlight such errors using conditional formatting, but this is very calculation intensive and doesn't scale well.

Be aware that multiple lookups of this nature can bring a workbook to its knees if used liberally, even without using conditional formatting.

All in all, for a small project where data entry is tightly controlled this is a flexible method of cascading drop down lists, but if you have little control over the users, or if the data collected will grow past a few hundreds lines then you'll probably want to look at validating the validation (!) using VBA, or just going the whole hog and forcing entry through a userform, which is by far the best option.

Edit: Example workbook

Edit again: If the data absolutely has to stay the way it is, then an advanced filter which creates the named ranges used in the validation would be required. At this level of complexity though you'd surely be better off just using a form for data entry.

Lunatik 2009-03-24 11:07:35

That example doesn't seem to restrict the choice of the next value based on the previous one, or am I missing something?

adolf garlic 2009-03-24 11:16:04

Yes, it requires a few ranges to be defined, but should give you a few pointers. I've added a link to a workbook that hopefully helps.

Lunatik 2009-03-24 11:37:06

That is the behaviour I'm after but it will take too long to set up as is. The underlying requirement is actually to allow users to select db->table->column for a reporting requirements template.In order to do it as shown, I will need to set up a range per table and there are hundreds.

adolf garlic 2009-03-24 12:54:57

In that case you're definitely better loading unique data into comboboxes and managing the dependencies in VBA.

Lunatik 2009-03-24 13:20:21

Answer 3

+1 A:

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

This approach does mean defining a unique set of values for each grouping, but this is trivial.

Thanks for the other ideas.

adolf garlic 2009-03-24 13:42:39

ansaurus

tags:

views:

answers:

xls cascading lookup based on pivot table style datasource?

related questions