Problems with Excel 2010 Data Validation

[henrytm82]

I'm having trouble with Excel 2010 throwing up errors when I try to use Data Validation to create a dropdown list on Sheet1 which references a list of names on Sheet2. I'm confused, and more than a little frustrated, because everything I read online says that what I'm doing should be working perfectly, and yet...

So here's what I'm doing:
On Sheet2 is a list of 53 names. I highlighted the range of names, and "named" the list "species". On Sheet1, I choose a cell and go to Data>Data Validation>Settings>Allow>List and then in source I type =Sheet2!species which throws up an error: "This type of reference cannot be used in a Data Validation formula." which is total BS - we already have another Excel workbook that uses exactly the same method, and every single thing I read online about it says what I'm doing is exactly right.

Can anyone tell me what in the world is going on, and how to fix it before I end up owing the university a computer and a window?

 

[kevatarvind]

just use

=species

 

[henrytm82]

just use

=species

"A named range you specified cannot be found."

 

[Brian from Maui]

You need to name your range either via Inert/Name/Define or Name Box, then use

=What Ever you named your range

 

[henrytm82]

You need to name your range either via Inert/Name/Define or Name Box, then use

=What Ever you named your range

Pretty sure I named it right. I attached a screenshot - is the range not named correctly?

 

[kevatarvind]

"A named range you specified cannot be found."

Then check your named range what you have named use that IF Still facing problem then upload your excel file with named range

i also using Excel 2010 and working for me

USE BELOW TO NAMED YOUR LIST

ALT + I + N + D

 

[Brian from Maui]

Try using =species instead of sheet2!species for naming the range

 

[markmzz]

Hi Henry,

Maybe this:

The name Species need to be a global name (Workbook) and not a local name (worksheet).

All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level). The scope of a name is the location within which the name is recognized without qualification.

Markmzz

 

[kevatarvind]

hi check below file

https://www.dropbox.com/s/290jl4t5j5n64fk/DATA VAILDATION.xlsx

 

[henrytm82]

Then check your named range what you have named use that IF Still facing problem then upload your excel file with named range

i also using Excel 2010 and working for me

USE BELOW TO NAMED YOUR LIST

ALT + I + N + D

Yeah, that's actually the method I used originally to name the range:

I have a feeling this may be something weird that's tied into permissions or something stupid like that. Our data manager used exactly the same method on another workbook on his own machine, and it worked exactly the way it should. I try to do it on mine, and get errors. I have absolutely no doubts at all that if I uploaded the workbook (which would be pointless because it's literally a brand new blank document, save for the list in the other worksheet that I'm trying to reference) you'd have no issues with getting it to work. I'm baffled by the errors, and extremely frustrated