Sum totals across different sheets

[budke44]

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>

 

[ztodd]

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

 

[budke44]

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]

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]

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]

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]

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.
Add other summaries as required.
Sum the information in the summary.

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

 

[budke44]

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