Can't get SUBSTITUTE Data Validations to work with OR function

[weasel28]

Hi All,

Can't seem to hit on the right combination for this one - I'm sure it'll turn out to be something obvious but I'm going round in circles here.
I'm using the SUBSTITUTE formula for data validation to produce dependent drop-down lists, but have run up against the 256 character limit for the DV box.
Therefore, I'm trying to shorten the statement by incorporating an OR function but can't seem to get it to work.

The first part of the Data Validation formula goes something like this:

=stuff_lk (where stuff_lk is a named range on a lookup sheet that contains the items to be used in the next drop down list)

So for example, column C in the data table will allow the selection of "phonetic", "numeral" plus a few others from a drop-down list as these are the values in stuff_lk

This is then used as a dependent value for the drop down list in column D of the data table, which has DV something like this:

=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C3,"Phonetic","phon_lk"), "Alphabetical","phon_lk"),"Letters","phon_lk"),"Numeral","num_lk"))

The idea being that the drop-down list selection in column D gives a set of values from the specific named range that relates to the value of column C. This works fine up until the point that I have had to add more values to column C and run up against the character limit for data validation.
As can be seen, some of the values in column C will actually share a corresponding value in column D.

For example, the values of "Phonetic", "Alphabetical" and "Letters" should all give values from phon_lk in column D via the data validation. This would cut down on the text in the DV box and not run up against the character limit if I could use some kind of OR statement.

E.g. =INDIRECT(SUBSTITUTE($C3,("Phonetic" OR "Alphabetical" OR "Letters"),"phon_lk"))

I know the syntax is completely wrong above, but it's just trying to demonstrate what I'm attempting to achieve.

I've looked at INDEX/MATCH and XLOOKUP but none seem appropriate as the named ranges for column D are all vastly different lengths and bear no relation to each other.

Is there a way to achieve this that I'm completely missing here? I've attached a small mock-up sheet using just a few SUBSTITUTE validations

Any help would be hugely appreciated.

Many thanks

Iain

[TMS]

You have to cheat.

1 Add a column to the tbl_primary
2 Insert the links to the secondary table(s)
3 Create a Named Range that refers to the body of the tbl_primary
in this example, dnrTMS
4 Use the DV formula: =INDIRECT(VLOOKUP($C3,dnrTMS,2,0))

See the updated example

[weasel28]

That's an excellent workaround - many thanks TMS. That was driving me up the wall.

Cheers

Iain

[TMS]

You're welcome. Thanks for the rep.

[TMS]

Note that the reason for doing it this way is that the DV doesn't seem happy about using INDIRECT, VLOOKUP and a Structured Table. And I seem to recall that it's not keen on using INDIRECT with Dynamic Named Ranges.

This approach means that, strictly speaking, you have a fixed Named Range but the size will automatically adjust if/when the Table increases in size.

The examples in the updated sample file demonstrate that, in theory, what you expect to work should work but doesn't.

[weasel28]

I do get the impression sometimes that DV is one of the features that MS have ignored for a while. Probably high time it got a revamp.