Data Validation using list in another worksheet - works for all but one sheet!

**GSumnerFan** · 12-30-2008, 11:48 AM

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

**JBeaucaire** · 12-30-2008, 11:52 AM

Post up the workbook and point out the NameRange that's causing the error and on which worksheet. We'll check it out.

**GSumnerFan** · 12-30-2008, 12:34 PM

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!