Re: Excel 2003 data validation across worksheets does not work

Did you precede the range name with = ?

Bill Manville
MVP - Microsoft Excel, Oxford, England

"Bill Manville" wrote:

> Did you precede the range name with = ?
>
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
>
> Bill,
Yes I did. I was working at a clients office and my notebook is on Office
pro 2003 and on the desktop is office pro 2002. I do the exact same thing on
office (ecel) 2002 and it works, on excel 2003 it does not work. I even
printed out the help screen on the subject and followed it step-by-step and
it only works on 2002.

Are there any settings in 2003 that I might be missing? Iam a system
engineer with almost 30 yrs experience and am completely baffled - the only
answer I have at the moment is that 2003 isnt working correctly.

Thanks! -> Dave

OK
I see the same behaviour in Excel 2003.

For some reason MS have always wanted to ban having validation lists
from other sheets or workbooks. The use of a range name exploited a
loophole - as you used to get that message on entering an explicit
range address in a different sheet/book for the list.

They seem to have plugged the loophole in Excel 2003.

However, if you define a name on the same sheet as the validation cell
and set the cell's validation list to reference that name, you can then
redefine the name to refer to a different sheet/book!

If the source workbook is open the validation list will work. If it is
closed it won't (and that was the same in 2002).


Bill Manville
MVP - Microsoft Excel, Oxford, England

Bill,

Thank you! I will try what you suggested. They will also need to update
their help files, because just for sanity's sake I printed out the help and
followed it step by step (even through i've been doing this validation for
years!) I am glad you were able to validate my problem, because nobody else I
know had 2003 to try it. 'm only using it because I have an MS subscription.

Thanks Again! -> Dave

"Bill Manville" wrote:

> OK
> I see the same behaviour in Excel 2003.
>
> For some reason MS have always wanted to ban having validation lists
> from other sheets or workbooks. The use of a range name exploited a
> loophole - as you used to get that message on entering an explicit
> range address in a different sheet/book for the list.
>
> They seem to have plugged the loophole in Excel 2003.
>
> However, if you define a name on the same sheet as the validation cell
> and set the cell's validation list to reference that name, you can then
> redefine the name to refer to a different sheet/book!
>
> If the source workbook is open the validation list will work. If it is
> closed it won't (and that was the same in 2002).
>
>
> Bill Manville
> MVP - Microsoft Excel, Oxford, England
>
>

Hi Dave & Bill,

I've found the exact problem Dave reported as well. I have my centralized
lists in one workbook, which is shared by the rest of the .xls files. It
works fine in Excel 2002, but NOT in Excel 2003.

You can reference the list in different sheet In Excel 2003 ONLY IF it is
within the same workbook, and, you MUST use a Name to reference the list.

Ultimately, what we need in here is the centralized file so that we just
need to make changes to the list items in that file only. Now Excel 2003
generates a run-time error in our code!

I don't know if they have a workaround.


"-> Dave" wrote:

> Bill,
>
> Thank you! I will try what you suggested. They will also need to update
> their help files, because just for sanity's sake I printed out the help and
> followed it step by step (even through i've been doing this validation for
> years!) I am glad you were able to validate my problem, because nobody else I
> know had 2003 to try it. 'm only using it because I have an MS subscription.
>
> Thanks Again! -> Dave
>
> "Bill Manville" wrote:
>
> > OK
> > I see the same behaviour in Excel 2003.
> >
> > For some reason MS have always wanted to ban having validation lists
> > from other sheets or workbooks. The use of a range name exploited a
> > loophole - as you used to get that message on entering an explicit
> > range address in a different sheet/book for the list.
> >
> > They seem to have plugged the loophole in Excel 2003.
> >
> > However, if you define a name on the same sheet as the validation cell
> > and set the cell's validation list to reference that name, you can then
> > redefine the name to refer to a different sheet/book!
> >
> > If the source workbook is open the validation list will work. If it is
> > closed it won't (and that was the same in 2002).
> >
> >
> > Bill Manville
> > MVP - Microsoft Excel, Oxford, England
> >
> >

I used Bills suggestion and it does work. I did not fully understand the
suggestion at first so it took a few tries to get it to work. I will explain
what I did below - it may work for you.

1) I created the worksheet that I needed the validation on.

2) On a different worksheet .in the same workbook< I created a short list of
data that I would use for validation (I just use 2-3 items)

3) I name that range

4) I put the validation on the real worksheet using the named range

5) I test the validation.

6) Save the workbook

7) Go to the where you named the range (insert/name) >and change the range
(don't redfine it- just change the range itself) you named to the external
worksheet where the real validation data resides.

This works fine for me.

BTW: Although I have the problem with Excel 2003, I have a customer that has
the problem with Excel 2002 with SP-3. Excell 2002 with SP-2 is OK still.

-> Dave



--
-> D


"coder" wrote:

> Hi Dave & Bill,
>
> I've found the exact problem Dave reported as well. I have my centralized
> lists in one workbook, which is shared by the rest of the .xls files. It
> works fine in Excel 2002, but NOT in Excel 2003.
>
> You can reference the list in different sheet In Excel 2003 ONLY IF it is
> within the same workbook, and, you MUST use a Name to reference the list.
>
> Ultimately, what we need in here is the centralized file so that we just
> need to make changes to the list items in that file only. Now Excel 2003
> generates a run-time error in our code!
>
> I don't know if they have a workaround.
>
>
> "-> Dave" wrote:
>
> > Bill,
> >
> > Thank you! I will try what you suggested. They will also need to update
> > their help files, because just for sanity's sake I printed out the help and
> > followed it step by step (even through i've been doing this validation for
> > years!) I am glad you were able to validate my problem, because nobody else I
> > know had 2003 to try it. 'm only using it because I have an MS subscription.
> >
> > Thanks Again! -> Dave
> >
> > "Bill Manville" wrote:
> >
> > > OK
> > > I see the same behaviour in Excel 2003.
> > >
> > > For some reason MS have always wanted to ban having validation lists
> > > from other sheets or workbooks. The use of a range name exploited a
> > > loophole - as you used to get that message on entering an explicit
> > > range address in a different sheet/book for the list.
> > >
> > > They seem to have plugged the loophole in Excel 2003.
> > >
> > > However, if you define a name on the same sheet as the validation cell
> > > and set the cell's validation list to reference that name, you can then
> > > redefine the name to refer to a different sheet/book!
> > >
> > > If the source workbook is open the validation list will work. If it is
> > > closed it won't (and that was the same in 2002).
> > >
> > >
> > > Bill Manville
> > > MVP - Microsoft Excel, Oxford, England
> > >
> > >