Microsoft Excel Data Validation Issue

Question

I tried to create data validation list with following function:

=OFFSET(INDIRECT(ADDRESS(3,MATCH(H$1,Dictionaries!$A$2:$AE$2,0),1,1,"Dictionaries")),,,COUNTA(INDIRECT(ADDRESS(3,MATCH(H$1,Dictionaries!$A$2:$AE$2,0),1,1,"Dictionaries")):INDIRECT(ADDRESS(301,MATCH(H$1,Dictionaries!$A$2:$AE$2,0),1,1,"Dictionaries"))))

But when I entered it into the source of data validation list program told me -  

"You may not use reference operators (such as unions, intersections and ranges) or array constants for Data Validation criteria."

Is there any solution to that??? NOPE????

Answer 1

Hi,

Try this

1. Enter the same function in the Name box.  Ctrl+F3 > New.  Assign a Name to it, say Rng_excel
2. In Data > Validation > Allow > List, enter =rng_excel in Source

Does this work?