Linking Files

[Guest]

I am using Excel 2003 and would like to do the following:

I have one file that I’d like to use as a list that all other files within
this project would refer to; this file is simply a list of billing reasons.

Within the multiple (~30) other files I would like to be able to use a drop
down list (in column N) that’s using the data in the “billing reasons†file.

I was informed that this may not work because the “billing reasons†file has
to be open whenever one of the other ~30 files is accessed. Since these are
files being used over a network, it isn’t feasible to make sure the “billing
reasons†file is open all the time.

So, I would like to be able to use a drop down list in multiple files that’s
using the data in the “billing reasons†file without needing the “billing
reasons†file to be open. If this can be done, how do I go about doing it?

Thank you in advance.

 

[DJH]

You could try:
Adding a worksheet in each of ~30 other files with links to your list within
the "Billing Reason" file.
Then use Data|Validation to your new worksheet list in each of the ~30 other
files.

 

[Guest]

That does work, but "reasons" are added to "Billing Reason" all the time, and
I'd prefer not to have to update the "Billing reasons" worksheet in each file
every time there's an addition to, or subtraction from that list.

Is there a way around having to this, or am I without any other options?

Thanks for your answer. It's much appreciated.

 

[Harlan Grove]

That does work, but "reasons" are added to "Billing Reason" all
the time, and I'd prefer not to have to update the "Billing reasons"
worksheet in each file every time there's an addition to, or
subtraction from that list.

....

You don't have to. If the list of billing reasons started in, say,

'X:\shared files\[shared tables.xls]Billing Reasons'!A2

and continued down for no more than, say, 100 rows, the worksheet
you'd add to the other workbooks would have cell A2 contain the
formula

Extra!A2:
='X:\shared files\[shared tables.xls]Billing Reasons'!A2

and fill A2 down into A3:A101. Then add a defined name like
BillingReasons referring to the formula

=Extra!$A$2:INDEX(Extra!$A$2:Extra!$A$101,
MATCH(TRUE,Extra!$A$1:$A$101<>"",0)-1)

Then use this defined name, BillingReasons, as your Data Validation
drop-down list source. This is a dynamic range that will adjust its
size depending on what the billing reasons workbook contains.

If this is still infeasible because some users may not have any access
to the file containing the current list of billing reasons, then
they'd either need to use a cached, possibly outdated list, or they'd
need to be prevented from using these other files until they got
access to the file containing the current list of billing reasons.
There really is no other alternative.