SUMIF and INDIRECT reference to tab in other workbook
 

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 !

SUMIF and INDIRECT reference to tab in other workbook
 

Is the workbook with the filename in L5 open?

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

DJL wrote:

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

SUMIF and INDIRECT reference to tab in other workbook
 

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" wrote:

Is the workbook with the filename in L5 open?

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

DJL wrote:

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
.

SUMIF and INDIRECT reference to tab in other workbook
 

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.

DJL wrote:

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" wrote:

Is the workbook with the filename in L5 open?

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

DJL wrote:

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
.


--

Dave Peterson