On Friday, September 9, 2016 at 12:13:44 PM UTC-5, GS wrote:
Have you considered using 'dependant' DV lists, where user selects
1st column and this determines what appears in the 2nd column's DV,
..and so on?

For example...

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

--
Garry

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

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

That's almost what I'm trying to do, with a slight variation.

All of those examples require separate lists to drive the dependent Validation List. I don't need separate lists, I need the final picklist to be sourced from the same data. Even the lower examples where they differentiate between yellow & red fruit require separate lists.

Imagine, within the example on the page you forwarded, that the user can either pick (in column B, as shown) "Fruit", OR they can leave column B blank & instead pick (within column A) "Fruit.Grocer" or "Fruit.FarmersMarket".

The Dynamic pick list in Column C needs to be the sourced from the same data range, regardless of how they select it.

i.e. whether the user picks "Fruit" in column B or they pick "Fruit.Grocer" in column A, Column D needs to show "Apple, Banana, Lemon, Peach"

In my case, as long as the user picks within column B, everything is fine. However, if they pick from column A, even after I've isolated the portion of the column A selection that just says "Fruit", my dynamic list doesn't return any rows.

The puzzling thing is that when I break out the formulas that calculate the OFFSET parameter, both methods result in the same values. That leads me to conclude that I don't have errors in those formulas and that it's got to either be a limitation in Excel or a configuration somewhere that I've missed.

I even checked to see whether Range Names would accept the Array format (Ctrl+Shft+Enter). BTW, they don't accept those....