Named Range Cannot Be found (even though it exists)

Question

Hi,I am trying to build conditional dropdowns where a selection in the first column drives the values in the dropdown of the second. I have several columns of data and have given each a nameI have created the first dropdown based on Division range and it works great&nbsp;However when I try to create a conditional list using Indirect I get the error - 'A named range you specified cannot be found)&nbsp;The Cell clearly says 'Education' and there is a Named Range called 'Education' as seen in the first screenshot, so no idea why I'm getting that error.&nbsp;If anyone can help I'd be eternally grateful and I have no idea what to try next and I've wasted ages trying to make this work&nbsp;

m_tarler · Answer

Make it an absolute reference $D$4=Indirect($D$4)&nbsp;

stevc999 · Answer

Sadly that doesn't work&nbsp;

stevc999 · Answer

Sadly that doesn't work...&nbsp;

stevc999 · Answer

I have resolved it - it was because I had R1C1 references switched on

pgsystemtester · Answer

Indirect has to have quotes around the desired range to be referenced, so that's probably your main issue.

Additionally, I've had some issues with conditional validation where i think you can only reference a range, not a calculated range -- but spill ranges are okay. Again, I am not 100% I've understood the limitation, but since spill range has always worked for me, that's been my route for such things. I've attached a sample file (no vba) where cell G4 has a country selection (Germany/USA) and the cell below G5 has data validation that's driven off of the above cell showing only cities within the country of choice.

Steps to create

Create list of US cities from B1:B3 of Boston, Atlanta, Dallas
Create German cities in C1:C2 of Munich, Berlin
In cell A1 enter this formula: =if(G4="USA",B1:B3,C1:C2)
Create a named range of listValidation that is =Sheet1!$A$1# (important to note the hashtag)
For you data validation rule in G5 set the validation rule for List =indirect("listValidation")

Further ideas are that you could have cell A1 be more dynamic, filtering all of columns b and c based on values not being blank. Best choice would probably be a single table column.

Forum Discussion

Named Range Cannot Be found (even though it exists)

5 Replies

Resources