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.