Can't carry totals over when copied from one sheet to another

[Dantro]

Hi, I have a time sheets which calculates my weekly totals for working, holidays and over time based on a week ending format. What I can't seem to work out is how to copy previous worksheet and have it update the week ending date and carry over previous weeks over time and holiday hours automatically.

Is there any way of achieving this? Many thanks for any assistance.

[tim201110]

file?_______________

[Dantro]

Thanks, hope this works.

Time sheet 2015 trial (1).xls

[Dantro]

The sheet I'm using is the last one.

[Pete_UK]

Each of your time sheets is slightly different, so you need to adopt a consistent style.

For the final sheet, you can use this formula in D33:

=INDIRECT("'"&TEXT(I5-7,"dd-mm-yy")&"'!d32")+D32

which will bring the data from the previous sheet, as defined by the date in cell I5, and add D32 onto it.

Hope this helps.

Pete

[Dantro]

Hi, Pete. Thank you for that it works well but I am also trying to automate the date changed and sheet name when I copy the sheet and add it in as a new week.

[Pete_UK]

If you tried to do that using formulae you would end up with a circular reference - you need the date of the current sheet to work out the date (and thus sheet name) of the previous sheet, so you can't have the date of the current sheet dependent directly on the date of the previous sheet.

Hope this helps.

Pete

[Dantro]

To gain the week ending date in IJK 5, I input the days date in K2 is there anyway of changing the sheet name which is the date like you said from that?

[tim201110]

Dantro,
imho, people confuse data bases and templates for printing. using db you've got all data in one sheet with such a wonderful option as autofilter and you can make a template using just index and match