Multi Sheet formula auto-populate?

**healimonster** · 05-13-2010, 08:18 PM

I have tallied some totals from several sheets to show a standings on each of those sheets. But the formula references sheets that doesn't exist yet and it doesn't work. because of that I had to create extra weeks down the road for the formula to work. It is for the baseball season and each sheet represents a week so I have to create about 20+ weeks. No big deal. But now I am trying to write out the formula and I am wondering if there is an easier way than typing out this formula for all these weeks. I manually typed it for the first few weeks but it is a pain in the but and I am wondering if I have to write the whole thing out for 20 some odd weeks.

=SUM('Week 1'!R9,'WEEK 2'!R9,'WEEK 3'!R9,'WEEK 4'!R9,'WEEK 5'!R9)

This is the formula I am using. Do I have to write it out for all the weeks? or is there an easier way to do this whole standings thing that I am hoping to add to each sheet (week).

I have attached the whole sheet for your viewing pleasure.

Thanks for any suggestions.

**teylyn** · 05-13-2010, 08:55 PM

Hello, without looking at your file:

this formula

=SUM('Week 1'!R9,'WEEK 2'!R9,'WEEK 3'!R9,'WEEK 4'!R9,'WEEK 5'!R9)

can be written as

=sum('Week 1:Week 5'!R9)

All sheets between Week 1 and Week 5 will be included. The sheet names don't matter.

**healimonster** · 05-14-2010, 09:48 AM

That is great. thank you Teylyn
Now is there an easy way to create 20 sheets named sequentially?

**teylyn** · 05-15-2010, 06:20 AM

Please Login or Register  to view this content.

**healimonster** · 05-17-2010, 12:21 PM

Ah Ha!

I figured out my problem. The ('start:finish'!R9) literally goes between the sheets and how they are arranged on the bottom of your workbook not the names of the sheets as you alluded to earlier. Being that week 5 has not happened and week 1 was the first week they were right next to each other on the right hand side of the work book. Hence only getting the week1 total.
Awesome. thanks again. Now I just need to organize my sheets a little better.

**teylyn** · 05-17-2010, 05:58 PM

I said above

=sum('Week 1:Week 5'!R9)

All sheets between Week 1 and Week 5 will be included. The sheet names don't matter.

If there are 100 sheets between sheet "Week 1" and sheet "Week 5" they will be included in the sum. The sheet names don't matter, just their position between the two named sheets.