Linking Spreadsheets for Data Validation

[Diane]

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

 

[Ron Coderre]

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)

 

[T. Valko]

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.

 

[Diane]

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

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)

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

 

[T. Valko]

Watch this video:

--
Biff
Microsoft Excel MVP

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

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)

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