SUMIF and INDIRECT reference to tab in other workbook

  • Thread starter Thread starter DJL
  • Start date Start date
D

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 !
 
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 !
Click to expand...
 
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:\

Dave Peterson said:
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 !
Click to expand...
Click to expand...
 
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:\

Dave Peterson said:
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 !
Click to expand...
Click to expand...
Click to expand...
 
Back
Top