[themisfitkitten]

I have a workbook for weekly timesheets... each week of the year has its own separate tab, and each tab has a "Week Of" cell on the worksheet. I didn't actually set this up, nor did I update last year's batch, but I think the person who did renamed each individual tab and Week Of cell. This will drive me crazy and take too long. Of course I know how to replace words (or the year) throughout the whole book, but is there a way to populate the appropriate dates with a formula or something? On the tabs, too.

Someone had recommended online time tracking programs/websites, but I've found myself at year-end without checking into this, and decided just to set up everyone's Excel timesheets until I can get a definite solution. If you have any suggestions, they are definitely welcome!

Happy New Year!

Tmk

[JBeaucaire]

I can't imagine a simple solution can't be gotten to. Post an example workbook and demonstrate exactly what you want to accomplish. BEFORE/AFTER if possible.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[themisfitkitten]

Thank you, JB!

I actually talked with one of my co-workers and explained what I needed the workbook to do. He had some free time to experiment with it and came up with something that is exactly what I needed! I'm all set. :D

Even so, I wanted to share this in case anyone might be able to steal it for their purposes. ;) All you have to do is modify the first tab with the first date of the week (this year it actually falls ON Jan 1), and fill in the employee's name. The rest of the workbook populates automatically with these things plus the specific days of the week as they are linked to the main date cell. The tabs are now named WEEK 1, etc. insead of 1/1-1/7, but this is perfectly fine by me.

I did have to drop it back to a .xls from a .xlsx, but it shouldn't affect the function.

-tmk

[JBeaucaire]

Some formula tips:

F29: =MIN(J29, 40)
H29: =MAX(0, J29-40)
J29: =SUM(F23:L23)


This last one is funny, there's a formula that lets you get the "sheetname" value.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

Since that exists, you can look at your sheetnames to determine the "number" in the sheetname, then use that to increment the number in O3 from Week 1.

So, this formula on Week 2 in O3 will give you the correct starting date based on the sheet name:

='WEEK 1'!O3 + ((MID(CELL("filename",A1), FIND(" ", CELL("filename",A1), FIND("]",CELL("filename",A1)))+1,256)-1)*7)


Click on Week 2, then SHIFT-Click on Week 53 to highlight all those sheets at once, then enter those formulas into the noted cells to update all sheets at once.

Now all your sheets are truly identical. Only the sheet name is different and causes the O3 dates to increment.