SUMIF and INDIRECT reference to tab in other workbook

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 !
 
D

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 !
 
D

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:\

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 !
 
D

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:\

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 !
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top