You will need the INDIRECT function. For example, if filename is in A1
and you know you will always refer to sheet Sales in any workbook and
seek cell F3, then

=INDIRECT("'C:\Myfolder\["&A1&"]Sales'!F3")

Note the single quotes needed in such cases. Caveat: the workbook must
be open or you get the #REF! error.

HTH
Kostis Vezerides

On Jul 6, 4:28 pm, RayportingMonkey
wrote:
I am building a report template that pulls like data from different vendor
workbooks. I want to be able to create "Dynamic Formulas" that can "Read"
what vendor I am working with and change based on that information.

For example;

Raw data may be contained in workbooks as follows:

RAWDATA_Vend1.xls
RAWDATA_Vend2.xls
RAWDATA_Vend3.xls

I am thinking that I would specify a location in the template to indicate
which vendor I am working with, (i.e. Vend2) and that criteria could be used
within formulas inside the worksheet, such as;

=SUM(A1+'[RAWDATA_?????.xls]TabName'!$A$2)
Where ????? = the content of my "criteria" cell.

I would want this reference to be used not only to reference external
locations, as described above, but also internally to:

Change report headers
Use in File Save As / In conjunction with a date
Change out images (if possible?!)

I don't even know if this type of stuff is possible, but it would make my
life sooo much easier as now I am modifying multiple templates every time I
have to implement a change. A single template would be much better!

Thanks in advance!
Ray