SUMIF and INDIRECT reference to tab in other workbook

[DJL]

I am trying to summarise multiple tabs onto one spreadsheet in a different
workbook that can have more than one row of data for the same criteria using
the following but it is returning with #REF as it is saying it is volatile.

$L$5 = Filename
$C10 = Tabname
$E$17:$E$28 = Range
$I$10 = Sumif Criteria
$I$17:$I$28 = Sum_Range

=SUMIF(INDIRECT("'["&$L$5&"]"&$C10&"'!$E$17:$E$28"),$I10,INDIRECT("'["&$L$5&"]"&$C10&"'!$I$17:$I$28"))

Any help greatly appreciated !

 

[Dave Peterson]

Is the workbook with the filename in L5 open?

Both =indirect() and =sumif() won't work if the sending workbook is closed.

I am trying to summarise multiple tabs onto one spreadsheet in a different
workbook that can have more than one row of data for the same criteria using
the following but it is returning with #REF as it is saying it is volatile.

$L$5 = Filename
$C10 = Tabname
$E$17:$E$28 = Range
$I$10 = Sumif Criteria
$I$17:$I$28 = Sum_Range

=SUMIF(INDIRECT("'["&$L$5&"]"&$C10&"'!$E$17:$E$28"),$I10,INDIRECT("'["&$L$5&"]"&$C10&"'!$I$17:$I$28"))

Any help greatly appreciated !

 

[DJL]

both files are open, however the files are on a memory stick and therefore L5
quotes the pathname as well as filename i.e. F:\

Is the workbook with the filename in L5 open?

Both =indirect() and =sumif() won't work if the sending workbook is closed.

I am trying to summarise multiple tabs onto one spreadsheet in a different
workbook that can have more than one row of data for the same criteria using
the following but it is returning with #REF as it is saying it is volatile.

$L$5 = Filename
$C10 = Tabname
$E$17:$E$28 = Range
$I$10 = Sumif Criteria
$I$17:$I$28 = Sum_Range

=SUMIF(INDIRECT("'["&$L$5&"]"&$C10&"'!$E$17:$E$28"),$I10,INDIRECT("'["&$L$5&"]"&$C10&"'!$I$17:$I$28"))

Any help greatly appreciated !

 

[Dave Peterson]

If the files are open, you don't need the drive or path.

I would suspect that the values that you're using don't match the names of the
file/sheet. I'd double check them by typing them from scratch.

If it still doesn't work after that, you should share the exact values in those
cells.

both files are open, however the files are on a memory stick and therefore L5
quotes the pathname as well as filename i.e. F:\

Is the workbook with the filename in L5 open?

Both =indirect() and =sumif() won't work if the sending workbook is closed.

I am trying to summarise multiple tabs onto one spreadsheet in a different
workbook that can have more than one row of data for the same criteria using
the following but it is returning with #REF as it is saying it is volatile.

$L$5 = Filename
$C10 = Tabname
$E$17:$E$28 = Range
$I$10 = Sumif Criteria
$I$17:$I$28 = Sum_Range

=SUMIF(INDIRECT("'["&$L$5&"]"&$C10&"'!$E$17:$E$28"),$I10,INDIRECT("'["&$L$5&"]"&$C10&"'!$I$17:$I$28"))

Any help greatly appreciated !