Linking Spreadsheets for Data Validation

D

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
 
R

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

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.
 
D

Diane

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

Ron Coderre said:
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 said:
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
Click to expand...
Click to expand...
 
T

T. Valko

Watch this video:


--
Biff
Microsoft Excel MVP


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

Ron Coderre said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top