# Sum totals across different sheets

#### budke44

##### New Member
Hi everyone, I'm using Excel 2013 and a little stumped. I have a weekly report I do to summarize inventory (I work for a bulk storage company, so we have product coming in and going out of tanks daily). I summarize this information by tank number and by week, month and YTD. Each week's information is listed on a separate sheet and that sheet is named the weekending date, for example today's report was on sheet named 09-18-2014, last weeks was 09-11-2014.

At the bottom of my report on the most recent sheet I have the totals

I'd show a screen shot but don't know how to post that on here, anyway this is what my summary looks like, I hope this makes sense, I've been manually adding to my sum formula in the monthly and YTD summary

 Tank 105 Tank 106 Opening Gauge 30 ft 10 1/2 10ft 2 1/4 Opening Barrels 50,550 13,000 Receipts 10,000 5,000 Deliveries 5,000 2,000 Closing Book 55,550 16,000 Closing Gauge 31ft 5 12ft 5 Closing Barrels 56,000 15,500 Totals Tank 106 (gain/loss) Tank 106 (gain/loss) Weekly 500 -500 Monthly 550 (adds weekly totals for month) 550 (adds weekly totals for month) YTD -750 (adds (weekly totals for year) 250 (adds weekly totals for year)

<tbody>
</tbody>

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### ztodd

##### Board Regular
What does your formula / do your formulas look like right now?

#### budke44

##### New Member
Well the weekly is just the (closing gauge) minus (opening gauge + reciepts - deliveries). my weekly is this =B42+'09-04-2014'!B42+'09-11-2014'!B42 and the YTD is this =SUM('09-18-2014:01-24-2014'!B42), B42 is my weekly total on each sheet for the year

#### budke44

##### New Member
My YTD formula doesn't work so well because I "move and copy" the last sheet to the beginning of the book, so I have to redo my formula to capture the new sheet

#### ztodd

##### Board Regular
So I assume your question is how you can modify your formulas so that you don't have to manually update them each week?

I haven't used that "range of sheets" method before in a formula, but maybe if you keep your sheet of the current week always having the same name, then that could resolve it for you if you used the "range of sheets" method in both formulas. (I don't know if that's what that method is really called- it's just what I call it)

#### budke44

##### New Member
Yes, I'm trying to make it so I don't need to update manually, not necessarily because I'm lazy mainly because the less I type the less chance of error. My sum with the range of sheets works well, maybe I can play with a little more and see what I'm missing, I'm wondering if there is a way to sumif possibly using the sheet names as criteria for my monthly summary

#### Dave Patton

##### Well-known Member
a) consider one sheet for data and Pivot Table for reporting on another sheet.

or

b) consider sum formula that totals cells across a range of sheets.

A blank sheet named Start is in front of relevant sheets. A blank sheet named End is after the last relevant sheet. New sheets are put between Start and End.

a simple example Opening balance =Start!B2

Receipts =SUM(Start:End!B3)

Deliveries =SUM(Start:End!B4)

Edit the references as necessary.
Sum the information in the summary.

Another idea would be use a formula to access the previous sheet.

Last edited:

#### budke44

##### New Member
Thanks Dave, I would love to use a pivot table, I'm actually trying working on that. My problem is the group this report gets sent to wants it formatted a certain way which is what is causing my issue. I appreciate all the help I'll keep working on it with your ideas

Replies
2
Views
162
Replies
0
Views
1K
Replies
10
Views
15K
Replies
2
Views
217

1,190,812
Messages
5,983,052
Members
439,817
Latest member
jessicabrown

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back