Linking Spreadsheets for Data Validation
 

Have several spreadhseets that will need to source a list from one
spreadsheet. Have already defined the source cells. When attempting to
validate the cells in the main document it will not allow me to use another
spreadsheet

Linking Spreadsheets for Data Validation
 

If you use a Named Range for the list source,
the Data Validation will accept it.

From the Excel Main Menu:
<insert<name<define
Names in Workbook: (Enter the name you want to use.....eg rngDVList)
Refers to: (select the list)

When you create the DV...
Allow: List
Source: (Press [F3] to see a list of Named Ranges...select yours)

If the list may vary in size due to additions/deletions,
you may want to use a Dynamic Named Range, which automatically expands
and contracts to accommodate the data.

See this website for instructions:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Diane" wrote in message
...
Have several spreadhseets that will need to source a list from one
spreadsheet. Have already defined the source cells. When attempting to
validate the cells in the main document it will not allow me to use
another
spreadsheet

Linking Spreadsheets for Data Validation
 

When you say "spreadsheets", do you mean worksheets within the *same*
workbook or do you mean separate workbooks?

If you mean worksheets within the *same* workbook then give the source a
defined name and then use that defined name. If you mean separate workbooks,
the source workbook must be open for it to work but you'd be better off
putting a source in each individual workbook.

--
Biff
Microsoft Excel MVP


"Diane" wrote in message
...
Have several spreadhseets that will need to source a list from one
spreadsheet. Have already defined the source cells. When attempting to
validate the cells in the main document it will not allow me to use
another
spreadsheet

Linking Spreadsheets for Data Validation
 

Received error message ~ You may not use references to other worksheets for
Data Validation criteria.

"Ron Coderre" wrote:

If you use a Named Range for the list source,
the Data Validation will accept it.

From the Excel Main Menu:
<insert<name<define
Names in Workbook: (Enter the name you want to use.....eg rngDVList)
Refers to: (select the list)

When you create the DV...
Allow: List
Source: (Press [F3] to see a list of Named Ranges...select yours)

If the list may vary in size due to additions/deletions,
you may want to use a Dynamic Named Range, which automatically expands
and contracts to accommodate the data.

See this website for instructions:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Diane" wrote in message
...
Have several spreadhseets that will need to source a list from one
spreadsheet. Have already defined the source cells. When attempting to
validate the cells in the main document it will not allow me to use
another
spreadsheet

Linking Spreadsheets for Data Validation
 

Watch this video:

http://youtube.com/watch?v=t2OsWJijrOM

--
Biff
Microsoft Excel MVP


"Diane" wrote in message
...
Received error message ~ You may not use references to other worksheets
for
Data Validation criteria.

"Ron Coderre" wrote:

If you use a Named Range for the list source,
the Data Validation will accept it.

From the Excel Main Menu:
<insert<name<define
Names in Workbook: (Enter the name you want to use.....eg rngDVList)
Refers to: (select the list)

When you create the DV...
Allow: List
Source: (Press [F3] to see a list of Named Ranges...select yours)

If the list may vary in size due to additions/deletions,
you may want to use a Dynamic Named Range, which automatically expands
and contracts to accommodate the data.

See this website for instructions:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Diane" wrote in message
...
Have several spreadhseets that will need to source a list from one
spreadsheet. Have already defined the source cells. When attempting
to
validate the cells in the main document it will not allow me to use
another
spreadsheet