Return Text or Data Validation List

[Entregan]

Workbook with more data than I can feasibly recreate with anywhere near a range of 20 x 20.
I can't share the workbook because of sensitive information.

That said, if help can be had, I'm all ears.

Scenario:

I have several Worksheets in my Workbook.
Worksheet 1 determines whether or not certain sources can be used for information or not.
Worksheet 2 is a form with many Dynamic Drop Downs.
Worksheet 3+ are all data sets that provide data for Worksheet 2, in the form of lists that remove all the blanks.

Problem:

These lists are all called through Data Validation.
The problem is that I need one of the cells (lets call it E15) to return either a static text, (that can be translated by another cell (E16 via Data Validation)
OR it (E15 needs to return a List via Data Validation, that can be read by E16 which will in turn produce a 2nd List of choices, or no choices at all.

Supplemental Information:
All of the lists items to be called are named ranges.
Some named ranges are being used with the EVALUATE function within the Name Manager.
All of this is based off of two values, that determine which range to use.
I am attempting to teach myself VBA. But it is slow going.

Thanks in advance to anyone attempting to help me.

[Entregan]

I should mention, I'm more than welcome to answer questions. There are just a series of dependent lists with VLOOKUP, HLOOKUP, and results that populate various lists.
In the simplest terms, I need to return either the name of a range, or the list of a dependent range.

[JeteMc]

Hard to determine without some type of example (fictional) however it sounds as in JBeaucaire's post (#2) in this thread may provide some helpful information.
Let us know if you have any questions.

[Entregan]

I've been working on an example. I have finally condensed enough that I "think" this would be acceptable.
Also, Thank you. That was more along the lines of what I was looking for.

[JeteMc]

I may be confused however this is what I came up with based on the sample.
Columns R, T and V are populated using:

=IFERROR(INDEX(K$2:K$10,AGGREGATE(15,6,(ROW($2:$10)-1)/(K$2:K$10<>""),ROW(1:1))),"")

Columns Q, S and U are populated using: =IF(R2="","",SUM(Q1,1))
Columns R, T and V are used as named ranges (i.e. Option.A, Option.B...)
A17:D17 are populated with drop downs based on F1:I1
A18:D18 are populated using: =IF(A17="", "", INDEX(INDIRECT(A17), 4))
They also can be populated using drop downs that reference the named ranges in F2:I7
A19:D21 are populated using drop downs that reference the named ranges from row 18
Let us know if you have any questions.

[Entregan]

Apparently the previous post I made didn't take.
I sincerely apologize for the confusion.
I have redone the example sheet. There is a little more information, and all the appropriate formulas, data validation and named ranges.
Hopefully that will clear up any further issues. (Sorry again...)

[JeteMc]

"Desired Effect is to switch between a simple reference, and a Dropdown list that matches one of the Option.X columns based on previously chosen list items dropdown1 and dropdown2" (from the file)
Based on the selections shown in the file attached to post #6, what should the drop down for cell A18 display? I am guessing Option.A, Option.B, Option.C and OptSelect because DropDown2 displays Type_A

[Entregan]

Yes.
And A19 would be a dropdown containing A.Select and so forth.

[JeteMc]

I believe that I have set up A18:A21 as you want.
Note that I don't think that OFFSET will work in the Option.x named ranges possibly due to the IF(COUNTIF... formulas used in columns K, M and O from which columns R, T and V are derived. (I attempted a couple of variations without success)
Let us know if you have any questions.

[Entregan]

I might be able to run with it. However, while I could implement the formulas listed, I'm not sure I follow how they work. I've not had experience with those functions INDEX,AGGREGATE.
I have always used LOOKUP Functions. And I have limited experience with the ROWS and COLUMN functions.
Ultimately I would need Category4 to populate as either Option.X or the List OptSelect

[JeteMc]

In the case of the INDEX/AGGREGATE formula used in R2
The first argument of INDEX, K2:K7, gives the lookup location
AGGREGATE is used to find the row number by returning the smallest, i.e. ROW(1:1), row in which K2:K7 is not blank.
The formula does basically the same, however since the second use of the ROW function now has the argument (2:2) it finds the second row number that is not blank (if there is one).
A good way to see what the formula is doing is to select a cell that contains the formula and then run the Evaluate Formula feature.
See if the rest of the range B19:D21 is filled in correctly.
Let us know if you have any questions.

[Entregan]

So, it translates like this?

=IFERROR(INDEX(Range_to_Reference,AGGREGATE(smallest,ignoring_errors,(ROW(range_of_rows)-1)/(Within_RANGE-Greater_or_Less_Than"",ROW(From_rows-1to1))),"")

Just to make sure I'm following.

Also:
D18 Should be equivalent to something to the effect of

=IF(OR($D$10="","OptSelect"),INDIRECT("OptSelect"),$D$10)

Where the Cells
D19,D20,D21 would be something like

=IF($D$18="OptSelect","",INDIRECT($D$18))

I am sorry this is such a pain.

[JeteMc]

I would translate the formula just a bit differently: =IFERROR(INDEX(Range_to_Reference,AGGREGATE(Kth-smallest,ignoring_errors,(ROW(range_of_rows)-1)/(Within_RANGE-not equal to "",ROW(From_rows-1to1))),"")
Note that ROW(From_rows-1to1) determines K as in ROW(1:1) is 1st smallest ROW(2:2) is 2nd smallest etc.
As to populating D18, it seems that you want "OptSelect" to populate the cell if D10 is blank or if D10 contains "OptSelect": =IF(D10="",J8,D10)
As to populating D19:D21 I may be confused, however those cells will be blank unless one of the "OptSelect" choices in made in one of those cells, so I don't believe that you need a formula there.
As to "such a pain", this is good stimulation for the neurons.

[Entregan]

Therein lies the problem. D18 needs to be able to switch back and forth between calling up the OptSelect list, or just the Option.(variable) to use as an indirect reference.
The subsequent cells D19:D21 need to populate only when D18 has one of the Option.(variable) is present.

I'm not sure I understand how all of the nested functions work. But, I do have a better grasp on the concept.

[JeteMc]

I think that we have it now.
The source for the data validation for cells D19:D21 reads: =IF(D$18="OptSelect",INDIRECT("fakelist"),INDIRECT(D$18))
When OptSelect is displayed in D18 the drop downs for D19:D21 will not display any choices.
When D18 displays Option.x then the available choices from that list will be displayed.
Let us know if you have any questions.

[Entregan]

There a way to keep, or reset that formula to return when the parent cells hold the appropriate value?
Also, INDIRECT would be nice. But, it doesn't work with Dynamic Lists. (or so I'm told)

Instead I'm using the Name Manager to Name the Cell "D17" in this case with a name
of Category4 referencing

=EVALUATE($D$18)

So that I can use Data Validation to use the simpler formula

=Category4

[JeteMc]

As to "a way to keep, or reset that formula to return when the parent cells hold the appropriate value". If you mean is there a way to have cells D19:D21 automatically display blanks if 'OptSelect' is again placed in D18 then no, they would have to be manually reset. Also remember that once an Option.x choice has been made from the drop down in D18 the formula will be overwritten. It may be possible using VBA, unfortunately I am illiterate when it comes to writing code. I'll ask the contributors from the VBA forum to take a look if you like.
As to naming cell D17, I am not sure what you are attempting to do. While cell D17 displays Category4, it doesn't appear to be a named cell in the file from which I have been working (attachment in post #15). Please provide further explanation of what functionality you would like for cell D17.

[Entregan]

I was afraid of that. I would appreciate any help you could acquire towards this end.

With regards to D17, the Name Manager there is a Name 'Category4' Value reads {...} Refers to reads '

=Evaluate(Sheet1!$D$18)

'

I was informed that doing this allows for the EVALUATE function (XML) to operate like INDIRECT does for dynamic ranges.
So that in turn Data Validation =Category4 will return the Evaluated list

[JeteMc]

I have asked VBA contributors to look at cell D18 and its dependents D19:D21 to see if there is a way to reset them using code.

I take it that cell D17 should be a drop down and should display the values in I2:I7 Option.D, Option.X, OptSelect and Option.T
I am guessing the since you mention dynamic ranges the remaining choices should be dynamic based on what is in in cell D18.
Would it be correct to assume that in this case you would also like cell D18 to return to a previous condition when/if cell D17 returns to displaying Category4?
Let us know if you have any questions.

[Entregan]

D17 functions like it is supposed to. My lists now work as necessary. short of the resetting.
D17 is just a header. The named range isn't necessarily tied to it. Just that it translates what would not otherwise be able to be translated.
D18 is the interloper to my current dilemma. I'm sure I will have more as a work my way to completion of this project. But, for now, D18 is my bane.
Thanks largely to you and your tireless efforts.