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

Ah.., you're trying to do this with a formula? ADO is coded so if you
post a link to a sample with the source data list and the expected
results on another sheet I'll see what I can do. If the source list
comes from a text file then include this in your link.

No degrading of your Excel skills was meant nor intended. I just meant
to explain the diff between using Access ways versus Excel ways at
different user skill levels. Also, I know award winning users of Excel
in the analystics field and most of them I consider wizards at what
they do. Haven't met any that can program Excel past macro recording.
Not saying you fit into this group of users, ..just saying!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion