sum() using indirect()

[Grymjack]

Can anyone post a formula that successfully uses
SUM(INDIRECT("concatenated cell range")) where the path isn't local to
the sheet it is on?

ex:

A1 = [TestBook.xls]Sheet1!B1
A2 = [TestBook.xls]Sheet1!B10

=SUM(INDIRECT(A1&":"&A2))

.....can you get that one to work??

[Biff]

Hi!

Make the 2nd reference just the cell address:

A1 = [TestBook.xls]Sheet1!B1
A2 = B10

=SUM(INDIRECT(A1&":"&A2))

Or, put the path in one cell and the cell references in other cells:

A1 = [TestBook.xls]Sheet1!
A2 = B1
A3 = B10

=SUM(INDIRECT(A1&A2&":"&A3))

Just note that the other file HAS to be open for this to work. If it's not
open you'll get a #REF! error.

Biff

"Grymjack" <[email protected]> wrote in message
news:[email protected]...
> Can anyone post a formula that successfully uses
> SUM(INDIRECT("concatenated cell range")) where the path isn't local to the
> sheet it is on?
>
> ex:
>
> A1 = [TestBook.xls]Sheet1!B1
> A2 = [TestBook.xls]Sheet1!B10
>
> =SUM(INDIRECT(A1&":"&A2))
>
> ....can you get that one to work??

[Grymjack]

Thanks Biff,
That did it!! Though I'm at kind of a loss why that will work when the
full path variable wont!?!

-Dan

Biff wrote:
> Hi!
>
> Make the 2nd reference just the cell address:
>
> A1 = [TestBook.xls]Sheet1!B1
> A2 = B10
>
> =SUM(INDIRECT(A1&":"&A2))
>
> Or, put the path in one cell and the cell references in other cells:
>
> A1 = [TestBook.xls]Sheet1!
> A2 = B1
> A3 = B10
>
> =SUM(INDIRECT(A1&A2&":"&A3))
>
> Just note that the other file HAS to be open for this to work. If it's not
> open you'll get a #REF! error.
>
> Biff
>
> "Grymjack" <[email protected]> wrote in message
> news:[email protected]...
>> Can anyone post a formula that successfully uses
>> SUM(INDIRECT("concatenated cell range")) where the path isn't local to the
>> sheet it is on?
>>
>> ex:
>>
>> A1 = [TestBook.xls]Sheet1!B1
>> A2 = [TestBook.xls]Sheet1!B10
>>
>> =SUM(INDIRECT(A1&":"&A2))
>>
>> ....can you get that one to work??
>
>

[Harlan Grove]

Grymjack wrote...
....
> That did it!! Though I'm at kind of a loss why that will work when the
>full path variable wont!?!
....
>>>A1 = [TestBook.xls]Sheet1!B1
>>>A2 = [TestBook.xls]Sheet1!B10
>>>
>>> =SUM(INDIRECT(A1&":"&A2))
....

Remove the SUM and INDIRECT calls and =A1&":"&A2 gives

[TestBook.xls]Sheet1!B1:[TestBook.xls]Sheet1!B10

This *is* a valid reference *EXPRESSION* if used directly in a formula.
That is, if TestBook.xls were open, the formula

=SUM([TestBook.xls]Sheet1!B1:[TestBook.xls]Sheet1!B10)

would return the same result as

=SUM([TestBook.xls]Sheet1!B1:B10)

But that's due to ambiguity in Excel's reference syntax. Colon, :, is
overloaded. It's used both in single area range references like B1:B10
and as a range accumulation operator in range references like A4:C5:F3,
which is equivalent to A3:F5. A rule of thumb is that when there's only
one colon in a range reference, and the right side of the range
reference is just a cell reference with no workbook/worksheet portion,
then the range reference is treated like a constant, but if there are
multiple colons or any cell reference to the right of any of the colons
includes a workbook/worksheet portion, the range reference is treated
like an expression. INDIRECT can handle range 'constants' but not range
'expressions', meaning what's on the right of the colon can only be a
simple cell address.