Indirect function across sheets
 

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?

Indirect function across sheets
 

Yes, it can. No, that is not correct. What formula can't you get to work?
--
David Biddulph

"Ken G." wrote in message
...
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?

Indirect function across sheets
 

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

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



--
__________________________________
HTH

Bob

"Ken G." wrote in message
...
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?

Indirect function across sheets
 

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

Yes, it can. No, that is not correct. What formula can't you get to work?
--
David Biddulph

"Ken G." wrote in message
...
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?

Indirect function across sheets
 

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

"Ken G." wrote in message
...
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" wrote:

Yes, it can. No, that is not correct. What formula can't you get to
work?
--
David Biddulph

"Ken G." wrote in message
...
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?

Indirect function across sheets
 

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

"Bob Phillips" wrote in message
...
With INDIRECT, the cells must then be in quotes, like

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



--
__________________________________
HTH

Bob

"Ken G." wrote in message
...
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?

Indirect function across sheets
 

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.

"David Biddulph" wrote:

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

"Ken G." wrote in message
...
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" wrote:

Yes, it can. No, that is not correct. What formula can't you get to
work?
--
David Biddulph

"Ken G." wrote in message
...
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?