Data Validation using list in another worksheet - works for all but one sheet!
I am running into an odd issue and hope someone might be able to shed some light.
I have created several lists on one sheet in a workbook and have assigned named ranges to each individual list. So far, so good.
On various other worksheets, I have created Data Validation rules to allow users to select the relevant data from drop-down lists. (using formulas such as =List1, =List2, etc. in the Source box to capture the named ranges I had created).
Everything works perfectly...EXCEPT for one worksheet, which continues to give me the error message, "You may not use references to other worksheets or workbooks for Data Validation criteria."
Can anyone explain why this error would only occur on one worksheet and work fine for all the other sheets? I'm perplexed!
Thanks for your help!
Heather
Last edited by GSumnerFan; 12-30-2008 at 11:37 AM.
Post up the workbook and point out the NameRange that's causing the error and on which worksheet. We'll check it out.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Thanks, JB! I think I figured it out anyway.
When I used the Go To function, all the named ranges appeared correctly on each worksheet except the troublesome one. When I opened the Define Name dialog box in that sheet, I found that some of the named ranges were somehow pointing directly to the drop-down cells I was trying to troubleshoot.
I deleted the references to these names and everything is working again!
I appreciate your quick offer of assistance!
If you took care of your issue, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks