Nov 22 2021 12:43 AM - edited Nov 22 2021 01:40 AM
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.
Nov 22 2021 12:50 AM
@David-W196 Can you upload a file with the type of validations you are trying to implement? Remove any sensitive information, of course.
Nov 22 2021 01:15 AM
@Riny_van_Eekelen - of course; I will create a basic file using the concepts - give me a short while; thank you.
Nov 22 2021 01:40 AM
Nov 22 2021 02:30 AM
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.
Nov 22 2021 02:52 AM
@Riny_van_Eekelen - thank you for the guidance; I will indeed look at UNIQUE and FILTER.
Nov 22 2021 03:47 AM
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!)
Nov 22 2021 03:48 AM
@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.)
Nov 22 2021 03:51 AM
Nov 22 2021 02:30 AM
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.