external refs and dropdown validation

[NJS]

Hi All.
When I follow the steps to create a dropdown based on a list in another
workbook I get "You may not use references to other workbooks for Data
Validation"?

Does this mean what it says, and is it not possible to use another workbook
as the source? I noticed some discussion between Michael and Massi, but this
didn't answer the primary question?

If I am obliged to refer to a local list linked to an external source, does
this not significantly increase the size of the local workbook?

thanks in advance.

 

[RagDyer]

Debra explains here:

http://www.contextures.com/xlDataVal05.html

 

[Harlan Grove]

When I follow the steps to create a dropdown based on a list in
another workbook I get "You may not use references to other
workbooks for Data Validation"?

Does this mean what it says, and is it not possible to use another
workbook as the source? I noticed some discussion between Michael
and Massi, but this didn't answer the primary question?

For my own convenience I'll refer to the workbook in which you want to
use a data validation list as A and the other workbook containing the
data you want the list to display as B.

If B were OPEN and you had defined a name in A referring to the range
in B that you want to use as the list source, you could set the data
validation list to refer to =YourDefinedNameHere. So, given these
caveats, you can use references into other workbooks.

OTOH, it's not possible to use direct references into CLOSED
workbooks. In such cases, your options are limited to filling a range
in A with the values from B, then use that range as the list source.

If I am obliged to refer to a local list linked to an external
source, does this not significantly increase the size of the local
workbook?

Depends on how much data you'd be fetching from B. If A would be a few
megabytes in size without the linked data, and the linked data added
less than, say, 250KB to the file size, that'd seem reasonable to me,
but this is subjective. OTOH, if your linked data would double the
file size or more, you need to reconsider your design.

 

[NJS]

Thanks very much all.

For my own convenience I'll refer to the workbook in which you want to
use a data validation list as A and the other workbook containing the
data you want the list to display as B.

If B were OPEN and you had defined a name in A referring to the range
in B that you want to use as the list source, you could set the data
validation list to refer to =YourDefinedNameHere. So, given these
caveats, you can use references into other workbooks.

OTOH, it's not possible to use direct references into CLOSED
workbooks. In such cases, your options are limited to filling a range
in A with the values from B, then use that range as the list source.


Depends on how much data you'd be fetching from B. If A would be a few
megabytes in size without the linked data, and the linked data added
less than, say, 250KB to the file size, that'd seem reasonable to me,
but this is subjective. OTOH, if your linked data would double the
file size or more, you need to reconsider your design.