• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically include data from new sheets created in my summary sheet calcs

J

j_sun

Member
Hi All,

I've been asked to track my employees' time in Excel using a new sheet for each week. The template/layout of all the fields on the sheet are identical week to week.


My questions is this:

Is there a way to have the values in new sheets I create for a new week automatically included in the calculations on the summary page I have put together to look at average and total times spent on various tasks?


The data cells will always be in the same location in each new sheet relative to all others.


Currently I have to go in and manually update my formulas on the summary sheet to include each new sheet I create.


To help illustrate, assume I am only tracking one item each week called Worked Time. It is entered in cell A1 on each sheet.

My Summary sheet will keep a grand total of Worked Time in cell A1.


So cell A1 in the Summary sheet will have the following formula:


=SUM(Week1!A1,Week2!A1,Week3!A1)


When next week comes and I create the Week 4 sheet I'd like the formula to automatically include cell A1 from it in this calc giving me:


=SUM(Week1!A1,Week2!A1,Week3!A1,Week4!A1)


Is there any way to automate this somewhat? It looks easy with just one formula but my summary sheet will actually have roughly 150 calculations on it.


Many thanks,

Jason
 
You could use a 3D formula to look at all the sheets, and we'll use some "bookends" to act as reference points.


First, we'll create the bookend sheets. These will be blank sheets, placed at beginning and end of your weekly sheets. E.g,

StartSheet, Week1, Week2, Week3, EndSheet


When you insert next weeks sheet, it would be

StartSheet, Week1, Week2, Week3, Week4, EndSheet


Now, the 3D formula is:

=SUM(StartSheet:EndSheet!A1)


You can hide the StartSheet and EndSheet for cosmetic purposes. With this setup, you can add/delete as many sheets as you want, and as long as they are between the two "bookends", your formula won't need to be changed.
 
Thanks Luke!!

I was expecting some long answer involving VBA or something but this is perfect. I'm sure I will get a lot of mileage out of this one!
 
You must log in or register to reply here.
Back
Top