New Contributor

So My boss came to me and wants me to fix a workbook so that each individual worksheet shows the corresponding days of the week Mon-Sun! with each worksheet continuing on the the following week and so on. I am using a sum function and made a cell the number 7 so that I could calculate the days of the week!! there has to be an easier different way!! PLEASE HELP!!!

3 Replies
Can you mock up the end result of what you're looking for?

@cuong This is what I am working on. All I want to do is be able to copy the data from sheet to sheet while changing the dates on said sheets without having to do each date individually?

Is this possible I am sure it is but I look like a dumb **bleep** to my boss right



You may define end of the week date in E1, under Sunday =E1, under Sat =T4-1, etc.


Thus copy/pasting the sheet you need only change the date in E1. Not sure about time and other data As for the sum under Monday current formula is

=SUM('P:\OutlookAttachments\[Wabash Manpower Schedule.xlsx]3-7-21'!A1)

You may change it with INDIRECT adjusting the path to the value in E1, but that requires that referenced file shall be opened. Perhaps that's not the case (not sure why do you need SUM for only one cell). The solution could be to generate formulas as above for all 54 weeks of the year in additional sheet which could be hided if necessary, and take results from these set of values. Possible errors for the future weeks could be handled by =IFERROR(..., "").