Need help creating a formula

[LGordon]

I'm using Excel in Microsoft 365. I am using Excel to keep track of 4 classes attendance/meal records. At the end of each month, I have to total the meals by free and paid. Each week, I have a total sheet and then the very last worksheet is a grand total of the meals for each day. I have each attendance sheet set up so that it looks like the attendance sheet and then the grand total looks like the sheet that I have to submit. So, I can't change the the order of the worksheets. I'm finding that each month, I'm having to re-do the formula for the Grand total sheet because each month's days' dates change. February 1st was on a Thursday, but March 1st was on a Friday. I change each work sheet to reflect the attendance sheets, but then because the days change on the worksheets, the formulas on the grand total sheet don't match. So, I'm manually entering the totals each month. Is there a way that I can do a formula that will recognize when the days change each month? I don't want to have the grand total worksheet read offhe weekly totals because then it doesn't provide a cross check on my numbers so I want it to calculate the worksheets rather than the total sheets. I would save so much time if I can figure out a formula that will help me with these calculations.

Thanks in advance,
Lisa

 

[Lutfor Rahman Shimanto]

I'm using Excel in Microsoft 365. I am using Excel to keep track of 4 classes attendance/meal records. At the end of each month, I have to total the meals by free and paid. Each week, I have a total sheet and then the very last worksheet is a grand total of the meals for each day. I have each attendance sheet set up so that it looks like the attendance sheet and then the grand total looks like the sheet that I have to submit. So, I can't change the the order of the worksheets. I'm finding that each month, I'm having to re-do the formula for the Grand total sheet because each month's days' dates change. February 1st was on a Thursday, but March 1st was on a Friday. I change each work sheet to reflect the attendance sheets, but then because the days change on the worksheets, the formulas on the grand total sheet don't match. So, I'm manually entering the totals each month. Is there a way that I can do a formula that will recognize when the days change each month? I don't want to have the grand total worksheet read offhe weekly totals because then it doesn't provide a cross check on my numbers so I want it to calculate the worksheets rather than the total sheets. I would save so much time if I can figure out a formula that will help me with these calculations.

Thanks in advance,
Lisa

Dear Lisa Gordon

Thanks for reaching out and sharing your query. Thanks once again for your patience. I have reviewed your requirements and found an excellent solution you will like.

You have mentioned that you are managing attendance and meal records for four classes. Each class has its own attendance/meal record sheet. There is a total sheet for each week, and the last worksheet is a grand total of meals for each day. At the end of each month, you have to calculate the total meals by free and paid categories. You also mentioned that you are not allowed to change the order of the worksheets.

You have to manually adjust formulas on the grand total sheet because the dates change for each month. So, you seek formulas to recognize when the days change each month and adjust accordingly. You also prefer the Grand Total sheet to calculate from the individual worksheets rather than directly from the total sheets to provide a cross-check on her numbers.

Don't worry! The solution I am going to present will save a lot of time on monthly calculations. I have implemented the requirements you mentioned within the Excel file you provided earlier. To fulfil your goal, I had to use some helper rows that will not typically show because I have made the text color white for better display, so do not clear any cell contents. I have tried to make the file without monthly dependencies; however, I am keeping the order of weeks, as you said. Within the system, you need to enter the attendance. Additionally, I have tried to improve all the previous formulas and make them as dynamic as possible. As I have developed lots of complex formulas and ideas within the file, I am just presenting solutions without explanations.

Overview of Week-level Entry

​

Overview of Week Total Report

​

Overview of Grand Total Report

​

Hopefully, you will love the solution. I have attached the solution workbook. Let us know after testing and reviewing the solution. Don't hesitate to reach out again if you have any questions regarding the solution.

Regards
Lutfor Rahman Shimanto
ExcelDemy