Hi all

I have a workbook that has validations applied to it and it works great!!!

I want to "change" the defined name in the source section to be a different
defined name/range, but I get this message when I enter :

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

I don't understand why it worked previously and why it doesn't work now.

I created this template back in December - would an upgrade affect it???

Other information:

To create the lists I used "defined names" as the lists are within another
workbook - this is the only way to do this...

To enter the defined name into the validation screen I used "name / paste"...

Under TOOLS / OPTIONS / CALCULATION the option to Accept labels in formulas
has been selected.

The excel template is saved on a Network along with the datafile where the
list lives.

If anyone can shed some light on why I can't do what I was able to do
previously please let me know asap.

--
Thank for your help
BeSmart

You are not trying to use a localised name by any chance, one whereby you
specify =Sheet3!rng in the DV? It doesn't seem to like this.

--
HTH

Bob Phillips

"BeSmart" wrote in message
...
Hi all

I have a workbook that has validations applied to it and it works great!!!

I want to "change" the defined name in the source section to be a
different
defined name/range, but I get this message when I enter :

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

I don't understand why it worked previously and why it doesn't work now.

I created this template back in December - would an upgrade affect it???

Other information:

To create the lists I used "defined names" as the lists are within another
workbook - this is the only way to do this...

To enter the defined name into the validation screen I used "name /
paste"...

Under TOOLS / OPTIONS / CALCULATION the option to Accept labels in
formulas
has been selected.

The excel template is saved on a Network along with the datafile where the
list lives.

If anyone can shed some light on why I can't do what I was able to do
previously please let me know asap.

--
Thank for your help
BeSmart

Besmart.

Data validation can only use defined names that a

global
local to the same sheet as the validation
uses indirect references(i.e. INDIRECT)

so say you have a list validation on sheet1

OK
List=Myname
List= sheet1!Myname
list=INDIRECT("sheet2!Myname")

Not OK
List = sheet2!Myname

DM Unseen

Hi Bob
No I'm not using a localised name..
In the Validation window I have a DEFINED NAME and the source field looks
like this:

=market

The Defined name of market has been created under INSERT / NAME / DEFINE and
the "refers to:" field looks like this:

=[AUDMasterList.xls]Markets!$A$1:$B$26

AUDMasterList.xls is the central file that opens automatically when the
template workbook is used, and it contains all my lookup and validation lists.

The current template workbook already has the validation list applied (ie
=market) and it work beautifully - I just want the list to look at a
different defined range of "marketlist" rather than "market"

The 'refers to:' field in NAME / DEFINE for Marketlist is:

=[AUDMasterList.xls]Markets!$A$1:$A$26

(One less column than for market)...
But when I enter that into the Validation source field I get the error
message quoted in my first posting...

It seems to me that the on one of my Windows Updates the programme has
changed and no longer accepts external files if they are entered as defined
names ??

--
Thank for your help
BeSmart


"Bob Phillips" wrote:

You are not trying to use a localised name by any chance, one whereby you
specify =Sheet3!rng in the DV? It doesn't seem to like this.

--
HTH

Bob Phillips

"BeSmart" wrote in message
...
Hi all

I have a workbook that has validations applied to it and it works great!!!

I want to "change" the defined name in the source section to be a
different
defined name/range, but I get this message when I enter :

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

I don't understand why it worked previously and why it doesn't work now.

I created this template back in December - would an upgrade affect it???

Other information:

To create the lists I used "defined names" as the lists are within another
workbook - this is the only way to do this...

To enter the defined name into the validation screen I used "name /
paste"...

Under TOOLS / OPTIONS / CALCULATION the option to Accept labels in
formulas
has been selected.

The excel template is saved on a Network along with the datafile where the
list lives.

If anyone can shed some light on why I can't do what I was able to do
previously please let me know asap.

--
Thank for your help
BeSmart