Build and execute dynamic instructions

[Smitty]

Is there a way I can create an instructuion that dynamically includes a file
name? For example, I have a number of formulas in a worksheet that calls for
data contained in cells in spreadsheet xxx in another workbook. Depending
upon the workbook I am provided I want to be able to substitute xxx with yyy
and execute the newly modified formula using the new workbook and worksheet
as input rather than that what was originally defined.

What I am trying to do is build forms that I store in a folder. The data on
the forms would be extracted and processed. I don't know the name of the
files (or how many I will be receiving) this month. Anybody got any thoughts
about how I might do this?

 

[galimi]

Smitty,

I have uploaded an example to http://www.nofavor.com/spreadsheets/xxx.zip.
It contains 3 files, master.xls, xxx.xls & yyy.xls. When you change the
value in cell B1 on the master.xls sheet to xxx or yyy, it will change the
formula in cell D1, essentially changing the reference point for your data.

 

[Shane Devenshire]

Hi,

If the other workbook is going to be open when you want the formula to work
then you should look at =INDIRECT(A1) where A1 contains the location you
want to get the data from.

If the files are going to be closed you can use
=CHOOSE(A1,"path\file1\sheet!range","path\file2\sheet!range","path\file3\sheet!range",....)

And then in A1 you enter the number that indicates which path you want to
use 1,2,3,...

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Smitty]

Galimi,

It worked fantastic. This will save me a ton of work each month.

Smitty