Indirect Function problem

[kfotedar]

I have a sumproduct formula which goes something like this..

SUMPRODUCT(('R:\TC
ANALYSES\Accounting\[MasterRentAccrualTables.xls]2017'!$A$14:$A$350=$A5)*('R:\TC
ANALYSES\Accounting\[MasterRentAccrualTables.xls]2017'!$C$14:$C$350>=J$4)*('R:\TC
ANALYSES\Accounting\[MasterRentAccrualTables.xls]2017'!$C$14:$C$350<=DATE(YEAR(J$4),MONTH(J$4)+1,0))*('R:\TC
ANALYSES\Accounting\[MasterRentAccrualTables.xls]2017'!$E$14:$E$350))

The above formula is in cell J5. In cell A5 of the same sheet I hav
the value 2017. Is there a way I could use the INDIRECT function t
substitute the sheet syntax "2017" in the above formula with th
Indirect function, which could pull it from cell A5.

Thanks,

Kavi

 

[Frank Kabel]

Hi
I would assume your other files are closed. If yes
INDIRECT won't work as it does not process closed files.

See:http://tinyurl.com/2c62u for some
alternatives. But I'm not sure that for example
IDIRECT.EXT will work in this case. Sou you may just test
it (haven't Morefunc.xll installed on this PC)

The formula would look like:
=SUMPRODUCT((INDIRECT.EXT("'R:\TCO
ANALYSES\Accounting\[MasterRentAccrualTables.xls]" & A5
& "'!$A$14:$A$350")=$A5)*(.....))

But even if this will work it will probably quite slow!!!

-----Original Message-----
I have a sumproduct formula which goes something like this..

SUMPRODUCT(('R:\TCO
ANALYSES\Accounting\[MasterRentAccrualTables.xls]2017'! $A$14:$A$350=$A5)*('R:\TCO
ANALYSES\Accounting\[MasterRentAccrualTables.xls]2017'! $C$14:$C$350>=J$4)*('R:\TCO
ANALYSES\Accounting\[MasterRentAccrualTables.xls]2017'!

$C$14:$C$350<=DATE(YEAR(J$4),MONTH(J$4)+1,0))*('R:\TCO

ANALYSES\Accounting\[MasterRentAccrualTables.xls]2017'!

$E$14:$E$350))

The above formula is in cell J5. In cell A5 of the same sheet I have
the value 2017. Is there a way I could use the INDIRECT function to
substitute the sheet syntax "2017" in the above formula with the
Indirect function, which could pull it from cell A5.

Thanks,

Kavir

 

[kfotedar]

FranK:

Thanks for your suggestion. You are right it does not work with closed
files and it does slow down the calculation process.

However, I did manage to make it work for one cell using the syntax
below..

But most likely will not use it..but its good to know.

=SUMPRODUCT((INDIRECT("'R:\TCO
ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!$A$14:$A$350")=$A5)*(INDIRECT("'R:\TCO
ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!$C$14:$C$350")>=J$4)*(INDIRECT("'R:\TCO
ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!$C$14:$C$350")<=DATE(YEAR(J$4),MONTH(J$4)+1,0))*(INDIRECT("'R:\TCO
ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!$E$14:$E$350")))

Thanks,

Kavir

 

[Harlan Grove]

Thanks for your suggestion. You are right it does not work with closed
files and it does slow down the calculation process.

However, I did manage to make it work for one cell using the syntax
below..

But most likely will not use it..but its good to know.

=SUMPRODUCT((INDIRECT("'R:\TCO
ANALYSES\Accounting\[MasterRentAccrualTables.xls]"&A5&"'!$A$14:$A$350")
=$A5)*(INDIRECT("'R:\TCO . . .

Since INDIRECT only works when the referenced workbook is open, and since
Excel only allows one file with a given base filename to be open at any
time, you *never* need to use the full pathname of the referenced workbook
in INDIRECT. Since it does nothing for you, it's better to eliminate it.

=SUMPRODUCT((INDIRECT("'[MasterRentAccrualTables.xls]"&A5&"'!A14:A350")=$A5)
*(INDIRECT("'[MasterRentAccrualTables.xls]"&A5&"'!C14:C350")>=J$4)
*(INDIRECT("'[MasterRentAccrualTables.xls]"&A5&"'!C14:C350")
<=DATE(YEAR(J$4),MONTH(J$4)+1,0))
*(INDIRECT("'[MasterRentAccrualTables.xls]"&A5&"'!E14:E350")))