Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Variables in Links | Excel Discussion (Misc queries) | |||
External Links not updating | Excel Discussion (Misc queries) | |||
Excel 2003: Temporarily "turning off" links to external files... | Excel Discussion (Misc queries) | |||
External links | Excel Discussion (Misc queries) | |||
External Links on open and Excel 2003 | Excel Discussion (Misc queries) |