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]

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

-Dan

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

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.