Forum Discussion
Double INDIRECT quandary - probably trying to do summat stoopid again...
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.
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.
8 Replies
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!)
- - that's where I got to - please see my reply Riny_van_Eekelen of Nov 22 2021 03:48 AM
David-W196 Can you upload a file with the type of validations you are trying to implement? Remove any sensitive information, of course.
- Uploaded.
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 - of course; I will create a basic file using the concepts - give me a short while; thank you.
