# Thread: Sum across sheets with INDIRECT()

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

3. ## 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. ## Re: Sum across sheets with INDIRECT()

Here, here. There are MANY 3D features Excel needs.

5. ## 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. ## 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
•