drop-down list error

[silvanet]

I followed the Excel Help directions to the letter creating a drop-down list with an outside spreadsheet reference, but when I went to finish, I got a pop-up error message saying "You may not use references to other worksheets or workbooks for Data Validation criteria." What's that all about? The instructions clearly say that you CAN use references to other worksheets or workbooks!

 

[erik.van.geit]

point 2. formulas should have been

='D:\herethepath\[Source.xls]Sheet1'!$B$2
='D:\herethepath\[Source.xls]Sheet1'!$B$3

 

[RoryA]

No, nothing is "missing." all the steps are in the actual help screen.

I didn't say you had missed a step out - I said those instructions were missing a step.

It just does not work. Please try it before you respond again.

It does work and I did check it before posting (using Excel 2003 SP3 to be sure). I will now leave you to your own devices.

 

[erik.van.geit]

I am not a moderator, but will try to tell something little.

It is clear that rorya felt silvanet's response as rude somehow.
silvanet, for me too it did sound rude, but perhaps you are not an english person? So if that is the case rorya will perhaps understand.
If you had a bad day, then you can tell it ... Else you will have a reputation which can be difficult to change. So people who could help, will perhaps leave you. Just my opinion and trying to stay human

 

[silvanet]

sweet looking solution...I'll try it...thanks.

 

[silvanet]

I am not a moderator, but will try to tell something little.

It is clear that rorya felt silvanet's response as rude somehow.
silvanet, for me too it did sound rude, but perhaps you are not an english person? So if that is the case rorya will perhaps understand.
If you had a bad day, then you can tell it ... Else you will have a reputation which can be difficult to change. So people who could help, will perhaps leave you. Just my opinion and trying to stay human

Wow!, if he felt offended, I'm really sorry. The only thing I meant was that if he actually tried to do what I documented he would receive the error I stated. I was very careful to follow the instructions in the Excel Help to the letter, and the result was not at all what the Help says. It was obvious to me that royra did not actually try it out, whatever the reason. I assumed it was because he missed something, as he stated. I didn't mean at all for him to become offended by my curt response. I was just trying to respond to everyone who replied...a lot of typing.

Anyway, my sincere apologies...to the group...and to royra. I wish he had said something to me before going off and complaining to someone about his perception of what I wrote. I used his exact same language to respond. He said he must have missed something, and I confirmed. I still believe that if he actually goes through step by step and does what the Excel Help says to do, he can reproduce the error message that I copied exactly from Excel and posted.

 

[silvanet]

I didn't say you had missed a step out - I said those instructions were missing a step.
It does work and I did check it before posting (using Excel 2003 SP3 to be sure). I will now leave you to your own devices.

There is obviously a mis-communication here. You cannot be doing what I'm doing and getting a different result. I didn't make up what I posted. I believe that you are doing the work within the same spreadsheet on different workbooks or tabs. Try placing your validation list in an entirely different, external, spreadsheet.

BTW, I'm sorry you were offended. I did not mean to do so.

 

[RoryA]

First off, yes I did find it rude, but then I'm recovering from a migraine so I'm not in the best of moods anyway. So let's ignore that and move on?
Second, you seem to have missed my point, which was that the Help files seem to be missing a step (not you), which was what I explained. If you follow the additional step I outlined, it will work. And to reiterate, I did test it and it does work. And no, I'm not using a sheet in the same workbook.
Finally, I did not go off and complain to anyone (that's not my style) - I responded directly to you and left it at that.

In any event, it seems you have a working solution now and that's all that matters!

 

[erik.van.geit]

It's fine that we have discussed our feelings.
Now we can move on together!

You cannot be doing what I'm doing and getting a different result.

I would always be very carefull saying that, because when it comes to computers, the slightest little detail can make a difference.

Do you have a working system now? What did you use?

 

[silvanet]

It's fine that we have discussed our feelings.
Now we can move on together!

I would always be very carefull saying that, because when it comes to computers, the slightest little detail can make a difference.

Do you have a working system now? What did you use?

See if you can reproduce this error message. I have done it again and again. I wish I could show you a screen shot. The Microsoft dialog box with the little yellow triangle having an exclamation point inside it reads exactly this:

"You may not use references to other worksheets or workbooks for Data Validation criteria."

what could be more clear than that?

Now, how did I get that?
1. go to Help, press F1
2. search for these words exactly "validation list"
3. select "create drop-down list from a range of cells"
4. skip down in the instructions to where it reads
"2. If you want to use another worksheet or another workbook, do one of the following:"
5. within that section, go down to where it reads
"Use a different worksheet in a different workbook Type the list on that worksheet, and then define a name with an external reference to the list." because that is exactly what I want to do
6. now, expand the +How? link
7. actually, there is no issue there...I think we can all agree how to do the steps 1-8 and define a validation list
8. continue on down to where the steps for creating a drop-down list continue at "3. select the cell where you want the drop-down list"
9. note: this is where the problem occurs...more precisely, at step number 6. which reads:

"To specify the location of the list of valid entries, do one of the following:

If the list is in the current worksheet, enter a reference to your list in the Source box.

If the list is on a different worksheet in the same workbook or a different workbook, enter the name that you defined for your list in the Source box.

In both cases, make sure that the reference or name is preceded with an equal sign (=). For example, enter =ValidDepts.

10. OK, by the way, the statement that in both cases you need to precede the name with an equal sign is false. You do not need to add it. It is redundant. If it works at all, it will work either with the reference name "ValidDepts" or "=ValidDepts."
11. In the case that your validation list is located in the same workbook, it works.
12. But, create a separate workbook, even within the same instance of Excel, and try to reference the list in the other workbook, whether you include or exclude the equal sign, you should be able to reproduce my referenced error message.

 

[RoryA]

Then no, I'm not doing what you're doing. I'm doing what I said you needed to do when I said that there was a step missing from those instructions! Define a name in the source workbook that references the list data you want to use. Then define a name in the workbook where you want to use the data validation, and have it reference the name defined in the source workbook. Then use the second name in the data validation.