Indirect function across sheets

[Ken G.]

If in "Sheet A" I have a formula =sum('Sheet B'!C1:C10) can the indirect
function be used on one of the arguments in the sum equation? I can't get it
to work so I'm thinking it can't be used to reference another sheet. Is that
correct?

 

[Bob Phillips]

With INDIRECT, the cells must then be in quotes, like

=SUM(A1&"!C1:C10")

 

[Ken G.]

If I start with the simple formula in my original post -
=sum('Sheet B'!C1:C10), but then want to use the indirect function to start
from a different cell in column C, how is the formula constructed?
I tried =sum('Sheet B'!Indirect("C"&D1+2):C10) and
=sum(Indirect('sheet B'!"C"&D1+2):C10) but both gave formula errors.

 

[David Biddulph]

Well, it isn't clear what cell you are trying to use to contain which part
of the original string from
=sum('Sheet B'!C1:C10)

It looks as if you are trying to use D1+2 to replace the 1 from the C1 cell
reference, which I suppose is OK if cell D1 contains a value of -1.

If that is what you are trying to do, then
=SUM(INDIRECT("'Sheet B'!C"&D1+2&":C10"))

You need to remember to put explicit text strings in quote marks, and use
CONCATENATE (or the & operator) to glue the final string together.
If D1 contains -1, then ="'Sheet B'!C"&D1+2&":C10" would return the string
'Sheet B'!C1:C10 which is what you are looking for inside the parentheses of
your SUM() function.

 

[David Biddulph]

I think that Bob intended to say
=SUM(INDIRECT(A1&"!C1:C10"))

 

[Ken G.]

Thanks Dave. Although I probably didn't explain it too well, you've explained
to me what I was doing wrong. It was the placement of the quotes that was the
problem. I knew they had to be there, I just had them in the wrong place.