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?

 

[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

Did you try a global name?

PS: your name is local (scope - sheet Species).

Markmzz

 

[henrytm82]

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

Okay, this right here might be my problem. I noticed that in the name manager, the "scope" is set to just the sheet the list is on, but if I try to edit the name range, it won't let me change the "scope".

EDIT: Okay, I deleted the range name, re-created it and was able to make the scope "workbook" and now it works. THANK YOU!

 

[markmzz]

Okay, this right here might be my problem. I noticed that in the name manager, the "scope" is set to just the sheet the list is on, but if I try to edit the name range, it won't let me change the "scope".

Yes, the Excel will not let you change the scope.

You need to create the name again.

EDIT: Okay, I deleted the range name, re-created it and was able to make the scope "workbook" and now it works. THANK YOU!

Ok and thanks for the feedback.

Markmzz

 

[henrytm82]

I love this website, and you guys are awesome. Thanks for helping me so fast, everyone