If you are currently using INDEX, VLOOKUP, MATCH, etc to retrieve data from external
worksheets, you've probably come across a situation where you would want to make the
external worksheet variable too!

Like for example, you have a report worksheet, that summarizes data from several biweekly 
or monthly reports.  However, every so often, you have to go through a painful process of
updating the links to point to the most current periods.

Then, there's the problem of relativity--what if you move the worksheet around, like send 
it to a friend's desktop.  The references you created may not work since they refer to 
files accessible to you.  Sure, your friend can tell Excel not to update links when they
open the file, but what if it happens accidentally? or what if they need to make an update?
 Now you get a workbook full of errors--simply because Excel didn't find the data it was
looking for.

Well, fret no more.  There is a way to combine the INDIRECt function with the INDEX, 
VLOOKUP and MATCH commands, and with a little help from a macro--
create a workbook with static formulas in the sheets that can all be repointed to 
a new file or location with a simple table update -manual or automated.

Our crude sample here contains all the elements you need to expand into your own 
application.  The most important is the method and the syntax.

Here's what the model does...

T2.xls contains a series of cells (C13-C16) on SHEET1 that are linked to cells in T1.XLS.
It contains a macro on a command button (could be an event) that
will scan the sheet, locate a file link in each cell reference, look up the file 
location in D12 and open it.  When you are ready for a new data source--just change 
the file name in cell D12 and re-execute the macro.  The new data will automatically 
populate the sheet.

To use this demo, you will have to change the file reference 
in T2.xls Sheet1 cell D12, because it is currently set up to open a file on my 
desktop, which I doubt you can access.  Change it to wherever you will be storing your 
T1.xls--and look carefully at the syntax--your file name must be entered in this format.

For help with a spreadsheet problem, or to have us create a user friendly, 
but time saving worksheet or workbook for you, please don't hesitate to contact us.  
Contact info is in workbook T2.XLS Sheet1.

