Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect Function() - summing across sheets | Excel Worksheet Functions | |||
INDIRECT for range of sheets | Excel Worksheet Functions | |||
INDIRECT and multiple sheets | Excel Worksheet Functions | |||
indirect function within sumif to reference other sheets | Excel Worksheet Functions | |||
Sum Indirect function through multiple sheets | Excel Discussion (Misc queries) |