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

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\Inc ome
Audit\Automation\[DailyInput.xls]InfoGenesis'!$B$10:$B$575=$B7)*('N:\Accounting\Inc ome Audit\Automation\[DailyInput.xls]InfoGenesis'!D$10:D$575))

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

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

Ryan---

--
RyGuy


"Mike H." wrote:

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\Inc ome
Audit\Automation\[DailyInput.xls]InfoGenesis'!$B$10:$B$575=$B7)*('N:\Accounting\Inc ome Audit\Automation\[DailyInput.xls]InfoGenesis'!D$10:D$575))

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

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.


--
Biff
Microsoft Excel MVP


"Mike H." wrote in message
...
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\Inc ome
Audit\Automation\[DailyInput.xls]InfoGenesis'!$B$10:$B$575=$B7)*('N:\Accounting\Inc ome
Audit\Automation\[DailyInput.xls]InfoGenesis'!D$10:D$575))