Forum Discussion
Double INDIRECT quandary - probably trying to do summat stoopid again...
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.
David-W196 Can you upload a file with the type of validations you are trying to implement? Remove any sensitive information, of course.
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.
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.)
Riny_van_Eekelen - thank you for the guidance; I will indeed look at UNIQUE and FILTER.