Saturday, August 13, 2011

yogi_Sum A Cell Across Multiple Sheets Within A Spreadsheet

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

8 comments:

  1. 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:

    * 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.

    ReplyDelete
  2. Hi Jeremy Olexa:

    I 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

    ReplyDelete
  3. Thanks, I'm having trouble with basic indirect() usage. With your advice, I'm trying to do:

    =INDIRECT(Sheet1!E17) but that gives me the error: "Argument must be a range" Unfortunately, the Google help page on indirect() is not helpful.

    ReplyDelete
    Replies
    1. =INDIRECT( CHAR(39)&H44&CHAR(39)&"!"&"D6"&":"&"D7" ) works.

      where 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.

      Delete
  4. HI Jeremy:

    =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

    ReplyDelete
    Replies
    1. Alright, thanks. I've reproduced your example and it works and I understand it. Something weird is happening.

      A9 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"

      Delete
    2. whew, I think I fixed it. There must have been some formatting in the cell that I could see.

      Thanks for your assistance and helpful attitude!

      Delete
    3. Good Job Jeremy ... looks like we are getting there.
      Now Let Us Keep Googling.

      Cheers!
      Yogi
      Cloud Computing -- Google Docs Way
      yogi--anand-consulting.blogspot.com

      Delete