Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
scottborys said ...
sum across multiple spreadsheets using INDIRECT
I have a variable-sized list of sheet names in A9:A100 corresponding to certain sheets in the current spreadsheet. I'm trying to sum the same cell from each spreadsheet. For example, this would work if I knew that I would always have two sheets:
sum(INDIRECT( CHAR( 39 ) & A9 & CHAR( 39 ) & "!E17" ), INDIRECT( CHAR( 39 ) & A10 & CHAR( 39 ) & "!E17" ))
However, due to the fact that the number of sheets will be changing rather frequently, modifying the formula each time isn't an option. Any thoughts on how to achieve the sum across multiple sheets using INDIRECT?
After that, I'll need to figure out how to drag the formula down vertically, ie, the next formula would be the equivalent of:
sum(INDIRECT( CHAR( 39 ) & A9 & CHAR( 39 ) & "!E18" ), INDIRECT( CHAR( 39 ) & A10 & CHAR( 39 ) & "!E18" ))
But we can cross that bridge when we come to it! Anyone have any thoughts on how to tackle this beast?
------------------------------------------------------
here we go ...
I have housed the cell of interest in B2
I have listed all the sheets that might be of interest in range A9:A -- I have listed the sheets of interest in cell A9 and down and I can go to cell A99 or even beyond depending on how many sheets are involved in computing the SUM
scottborys said ...
sum across multiple spreadsheets using INDIRECT
I have a variable-sized list of sheet names in A9:A100 corresponding to certain sheets in the current spreadsheet. I'm trying to sum the same cell from each spreadsheet. For example, this would work if I knew that I would always have two sheets:
sum(INDIRECT( CHAR( 39 ) & A9 & CHAR( 39 ) & "!E17" ), INDIRECT( CHAR( 39 ) & A10 & CHAR( 39 ) & "!E17" ))
However, due to the fact that the number of sheets will be changing rather frequently, modifying the formula each time isn't an option. Any thoughts on how to achieve the sum across multiple sheets using INDIRECT?
After that, I'll need to figure out how to drag the formula down vertically, ie, the next formula would be the equivalent of:
sum(INDIRECT( CHAR( 39 ) & A9 & CHAR( 39 ) & "!E18" ), INDIRECT( CHAR( 39 ) & A10 & CHAR( 39 ) & "!E18" ))
But we can cross that bridge when we come to it! Anyone have any thoughts on how to tackle this beast?
------------------------------------------------------
here we go ...
I have housed the cell of interest in B2
I have listed all the sheets that might be of interest in range A9:A -- I have listed the sheets of interest in cell A9 and down and I can go to cell A99 or even beyond depending on how many sheets are involved in computing the SUM
Hi there, thanks for helping the GDocs community, I've seen many of your posts. However, I have a question. I think this page explains how to do what I want but I'm missing something. So, here is what I want to do:
ReplyDelete* Sum D38 in every sheet. I'll be adding sheets at random and the number of sheets is not fixed. One way to do that is:
=SUM(Sheet1!D38+Sheet2!D38)
but that means that I need to add "SheetX!D38" every time I have a new sheet, which is not ideal as I will have up to 50 sheets.
I don't understand what =indirect("'"&A9&"'!"&B$2) actually does.
Hi Jeremy Olexa:
ReplyDeleteI don't understand what =indirect("'"&A9&"'!"&B$2) actually does.
if cell A9 houses the Sheet name, let us say Sheet1, and cell B2 houses E17
then the referenced formula in cell B9 pulls the value from cell:
Sheet1!E17
and with Sheet2 in cell A10, the referenced formula in cell B10 pulls the value from cell:
Sheet2!E17
and so on.
Jeremy, I hope this helps.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Thanks, I'm having trouble with basic indirect() usage. With your advice, I'm trying to do:
ReplyDelete=INDIRECT(Sheet1!E17) but that gives me the error: "Argument must be a range" Unfortunately, the Google help page on indirect() is not helpful.
=INDIRECT( CHAR(39)&H44&CHAR(39)&"!"&"D6"&":"&"D7" ) works.
Deletewhere H44 houses "sheet1" and D6 is the value I want on sheet1. Can you explain this better and how your example acomplishes this. I'm confused on what your example now because of the range issue.
HI Jeremy:
ReplyDelete=INDIRECT(Sheet1!E17) but that gives me the error: "Argument must be a range" Unfortunately, the Google help page on indirect() is not helpful
Indirect function does not work this way ... follow the approach I talked about in my response to your first comment.
Let me suggest that to get a clear understanding of this formulation, I suggest you try to replicate what I have done in this blogpost ... what I mean is create a spreadsheet similar to the one shown in my blog post, and make sure you can get the same result that I got for the spreadsheet in my blog post -- by the time you have done that you will have a clearer understanding of how INDIRECT function works and why we are using the INDIRECT function in this case.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Alright, thanks. I've reproduced your example and it works and I understand it. Something weird is happening.
DeleteA9 houses: "sheet1"
A10 houses: "D6"
H43 houses: "D6"
=indirect("'"&A9&"'!"&A10) works (gives me the value of "sheet1!D6")
=indirect("'"&A9&"'!"&H43) gives error: "Argument must be a range"
whew, I think I fixed it. There must have been some formatting in the cell that I could see.
DeleteThanks for your assistance and helpful attitude!
Good Job Jeremy ... looks like we are getting there.
DeleteNow Let Us Keep Googling.
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com