Double INDIRECT quandary - probably trying to do summat stoopid again...

[David-W196]

Hi Everyone - thank you for looking at this question.

I am trying to do something 'clever' - and appear to be missing the mark!  I am using a dependent drop-down (using INDIRECT within Data Validation: which works) so that a selection in column 1 provides the user with a limited selection of options in column 2.

I am also using INDIRECT in Name Manager, to define a named range according to the content: so as a list of items grows, the named range extends.  Again, this works for a simple drop-down.

The issue arises when I try to combine the two above systems: the drop-down in column 2 cannot seem to comprehend the 'volatile' named range and returns #REF!

On the basis that I do not have the option to use VBA (file size AND IT system security limitations) am I simply attempting something impossible, please?  Thank you in advance.

[#2]

@David-W196 Can you upload a file with the type of validations you are trying to implement? Remove any sensitive information, of course.

[#3]

@Riny_van_Eekelen - of course; I will create a basic file using the concepts - give me a short while; thank you.

[#4]

Uploaded.

[#5 · Accepted solution]

@David-W196 As I see it, what you call the volatile dropdown list refers to a named formula FRU. List in data validation must refer to ranges, named or direct references. 

Since you tagged your original post with Office365, why not look into the functions UNIQUE and FILTER as demonstrated in the attached file.

[#6]

@Riny_van_Eekelen - thank you for the guidance; I will indeed look at UNIQUE and FILTER.

[#7]

@David-W196 

If you start with @Riny_van_Eekelen 's table, the columns may be made to be mutually dependent rather than a hierarchical dependence.  It means that the options need to be cleared before setting out to choose something new but it does prevent the user from selecting

Salad - Beer

(as long as no-one puts mayo in my beer!)

[#8]

@Riny_van_Eekelen - although this works for a one-off situation (as the dependent drop-down requires a dedicated space for the UNIQUE and FILTER calculation), I am using the dependent drop-down across thousands of rows, each one allowing a user to select different 1st and 2nd options - so this doesn't work for my need.  But an excellent idea for other situations, thank you!

I think the conclusion I am drawing from this exercise is that although a pre-named range can be selected as Option 2, a named range must be fixed for INDIRECT to work in data validation.  Which is disappointing, but not surprising.  (The intent was to allow the named range to flex to future-proof the addition of future items in Option 2 lists, without having multiple spaces showing in the Option 2 drop-down list.)

[#9]

- that's where I got to - please see my reply @Riny_van_Eekelen  of Nov 22 2021 03:48 AM