Variables in links and external file references
 

I have a multiple worksheet workbook that pulls data in from several
other worksheets in workbooks. Each worksheet in my workbook is
consistent in look and format except for the data it shows - each
sheet represents a different cost center number. I am referencing
other files that use the same scheme, e.g.,
"'c:\accounting\[workbook]9440'!A1" pulls data from
"'s:\data\[masterfile]9440'!B762".

Is there a way to use variables in the filename/reference? I want to
insert the worksheet name (9440, 9884,3325, etc.) displayed on the tab
into the reference, something akin to
"'c:\accounting\[workbook]TABNAME'!A1. This will make adding sheets
very easy since the tab name in my file refers to the tab name in the
external file.

I can't figure out how to do variables in links.

Nate wrote:
Is there a way to use variables in the filename/reference?


You can use the INDIRECT function but it only works if the source files
are open and so is of limited usefulness in a case like yours.

Example, assuming the cost centre number is in B2:
=INDIRECT("'s:\data\[masterfile]" & B2 & "'!B762")

I would suggest it is easier to make your new cost centre by copying an
existing sheet (say for 9440) and then
Edit / Replace / ]9440' [with] ]9441' / Replace All

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup