Variabilize File Path or File Name in SumProduct (and Vlookup too)

[Mike H.]

In the formula at the bottom, is there a way to "variabilize" the file name
and path so that based on the value of cell the path might be
n:\accounting\income audit\automation\lastmonth\[dailyinput.xls]

or perhaps the filename would be different:

n:\accounting\income audit\automation\[dailyinputDec.xls]


=SUMPRODUCT(('N:\Accounting\Income
Audit\Automation\[DailyInput.xls]InfoGenesis'!$C$10:$C$575=$A7)*('N:\Accounting\Income
Audit\Automation\[DailyInput.xls]InfoGenesis'!$B$10:$B$575=$B7)*('N:\Accounting\Income Audit\Automation\[DailyInput.xls]InfoGenesis'!D$10$575))

 

[ryguy7272]

Can you replace '[dailyinput.xls]' with A1 (cell reference) and then change
the value in A1 when needed?

Ryan---

 

[T. Valko]

You can do this using the INDIRECT function. However, this *requires* that
the referenced file(s) *MUST* be open. This is usually not desireable.

A possible workaround is to download the *free* add-in, Morefunc.xll from
this site:

http://xcell05.free.fr/morefunc/english/index.htm

It has a function called INDIRECT.EXT that works the same as the built-in
INDIRECT *except* it will work on closed files.