Hi GS,

I understand what you are saying yet the fact remains that Excel cannot handle the data in the way it arrives, cannot process it as required, and therefore cannot provide a suitable solution to the problem.

If time permitted to completely refactor the data into separate chunks in order to overcome Excels lack of ability to do a conditional cascaded dynamic validation list. Effectively it lacks a WHERE clause to apply multiple conditions the results in more than a single cells worth of data being returned.

The nearest that can be achieved is something like this:
=INDEX(field_you_want-returned,MATCH(1,(criteriaField1=$B2)*(criteriaFie ld2=$C2)*(criteriaField3=$D2),0),1) as an array formula.

This of course will only return the first result encountered. There is no actual way in Excel to achieve this type of functionality returning multiple results.

I am happy to be proven wrong on this, but so far nothing that has been suggested here achieves this not have I been able to locate a suitable approach on the web. Dynamic named ranges do not work with ADO. So what is the alternative except to break the data up into lists, then lists of lists and so on in order to facilitate this. Utterly useless in this scenario.

As for my experience with Excel, I have worked as a professional developer designing and building analytical tools for global companies for years. I am quite aware of Excels abilities. My hope was that in a newer version of Excel, in this 2013, that the ability might have finally been included as a feature of the application, but it hasn't.

If you know a way to achieve the above formula with multiple results being returned such that a data validation list can be generated then I am all ears. The nearest I have managed to achieve is to use a type of secondary dynamic list for each stage of the criterion 'filtering'. The more cascades you need the less feasible it becomes such that by the time you hit a third criteria your number of supporting lists grows exponentially. Wholly unsupportable in the real world.

So, back to the point: Excel is not suited to this problem.

The Frog