Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2001
    Location
    Michigan, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum across sheets with INDIRECT()

    Greetings:

    I'm trying to create a Year-to-date sheet summing across 12 sheets named January, February, etc., and I would like to make the name of the last tab a variable in an INDIRECT() statement to create the year-to-date total.

    the regular statement would be:
    =sum(January:February!a1)
    but so far I've been unable to work out the syntax to sum across the sheets with INDIRECT(). I've tried
    =sum(indirect("January:"&LastMonth&"!A1))
    without success. Is it even possible to use INDIRECT() in this fashion?

    Any tips, hints, or pointers greatly appreciated.

    Thanks

    Mark

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sum across sheets with INDIRECT()

    Mark, I use =indirect() a bit, and was merely able to reproduce the problem, that is, I likewise can't do what you want. I came up with a sloppy alternative for your consideration, attached. (I tested with only three months, the choose formula would have to be extended for the entire twelve.)

    HTH, but probably not.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Apr 2001
    Location
    St. Louis, Missouri, USA
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum across sheets with INDIRECT()

    I've also tried to do this without success. Apparently, we can create the text string with the reference, but INDIRECT can't change this text to a 3-D reference. (Microsoft--This would be a good enhancement for the next Excel.) If this is the case, then we probably don't have a way to assemble the reference.

    A fairly simple (but not so elegant) solution is to create all your 3-D formulas referring to the same sheet range (Janec!A1) and then do a Replace on the sheet, replacing "ec!" with ":Apr!" or whatever month. Just use enough characters to identify the reference so that you don't change non-references.

    You could record a macro of the replacement, then change it to get the ending month from a cell on your control sheet. Assign this to a button, and you will have an easy way to update your references. Not quite the same as automatic links, but practically the same functionality.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Sum across sheets with INDIRECT()

    Here, here. There are MANY 3D features Excel needs.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sum across sheets with INDIRECT()

    John Walkenbach has a User Defined Function or two at <A target="_blank" HREF=http://j-walk.com/ss/excel/tips/tip63.htm>The Spreadsheet page</A> that may help you.

    Still no substitute for the lack of built in support.

    Andrew C

  6. #6
    Lounger
    Join Date
    Apr 2001
    Location
    Michigan, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum across sheets with INDIRECT()

    Thanks to everyone for their input.

    It appears that the INDIRECT() route is not the way to go.

    Thanks again.

    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •