[Solved] Meal Tracker in Excel for Different Classes

[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

 

[LGordon]

Wow! This looks like it will work!!!! Thank you so much! Some months there are less students and others more students. How will adding and removing rows affect the formula? Right now the sheet has no actual names, but what if I put in their names and then sort them alphabetically, will the formula remain? Thank you so much.

 

[Lutfor Rahman Shimanto]

Wow! This looks like it will work!!!! Thank you so much! Some months there are less students and others more students. How will adding and removing rows affect the formula? Right now the sheet has no actual names, but what if I put in their names and then sort them alphabetically, will the formula remain? Thank you so much.

Dear Lisa Gordon

Thanks for your kind words! You are welcome. We are glad it worked on your end.

Adding and removing rows will affect the formulas. However, suppose you add and remove rows between the ranges instead of adding and removing them at the end or beginning of the range. In that case, Excel will automatically adjust the formulas. When it comes to sorting, the formula will not be affected.

Adding and Removing Rows:

​

Sorting Rows Alphabetically:

​

Hopefully, these ideas will help; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[mibr fan token]

Youre so cool! I dont suppose Ive learn anything like this before. So nice to find somebody with some unique thoughts on this subject. realy thank you for starting this up. this website is one thing that is wanted on the internet, someone with a little originality. helpful job for bringing one thing new to the web!

 

[shamimarita]

Thanks for your appreciation. We are glad to hear that you found our website helpful.

 

[LGordon]

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

View attachment 1371​

Overview of Week Total Report

View attachment 1372​

Overview of Grand Total Report

View attachment 1373​

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

Dear Luftor Rahman Shimanto,

Thank you so much! This has helped me. I'm having a problem with the formula for the Grand Total sheet - May 2024. Lines 7 and 8 which is May 2nd and 3rd is not calculating correctly. All of the other lines are correct.

 

[shamimarita]

Dear Luftor Rahman Shimanto,

Thank you so much! This has helped me. I'm having a problem with the formula for the Grand Total sheet - May 2024. Lines 7 and 8 which is May 2nd and 3rd is not calculating correctly. All of the other lines are correct.

Hello Lisa Gordon,

To calculate the mentioned date correctly, updated the existing Excel file. Changed the relative references to absolute references.




Here, is the Excel File:

 

[LGordon]

Thank you for your help. Using the fixed spreadsheet that Shamimarita uploaded for me, I completed the June sheet. All the sheets worked great with the exception of the Grand Total sheet. It isn't calculating anything. I've uploaded my completed sheet. Maybe you can see what is wrong. I even tried using the sample sheet again. I renamed it, entered in some data but still the Grand Total won't calculate. Am I missing a step? It's been a month since I've worked on this so maybe I've forgotten to do something?

Please have a look at the attachment and thank you so much for your help.

Lisa

 

[shamimarita]

Hello Lisa,

The reason the Grand Total sheet does not work is that in the system workbook, the week number starts from 0 to 4. You have started it from 1 in the workbook.
Num week : 0 for week1
Num week: 1 for week2
Num week: 2 for week3
Num week: 3 for week4

Adjusted the workbook for June 2024. Please make a copy of this workbook and adapt it for various months.

 

[LGordon]

Thank you so much for your help. I knew it was something I wasn't doing correctly that would fix it. Again, this has helped me immensely. Thank you. Thank you.

Lisa

 

[shamimarita]

You're most welcome! I'm glad I could help and that it made a difference. Don't hesitate to reach out if you have any more questions or run into any other issues. We're all here to help each other make the most of Excel and improve our skills!

 

[LGordon]

You're most welcome! I'm glad I could help and that it made a difference. Don't hesitate to reach out if you have any more questions or run into any other issues. We're all here to help each other make the most of Excel and improve our skills!

I've run into a few issues with the spreadsheet that I just did a workaround for because I didn't have time to figure out what was wrong nor the ability

1 - not an issue right now, but the last year on this is 2025. How do I adjust it for the future years? When created, it was created using past years and only 1 future year. I looked back through the years to see if there is another year where March 3rd is a Monday and I didn't see one. Looking back at a calendar, the last time March 3rd was a Monday, it was 2014.
2 - the highlighted cells are cells where the formula didn't calculate. In the interest of time, I just created a work around using the numbers on the right and add the formula manually. I don't know why it stopped calculating.
3 - the highlight of blue shows a "REF" - the whole line shows it
4 - the last time the Grand Total sheet wouldn't calculate, it was because I started with Week 1 instead of Week 0. For March, when I choose week 0, there are no dates. They're all xx's for the dates. Shouldn't week 0 show the first full week in March rather than no dates? Because it's not showing dates, March 31st isn't included in the formulas because it is on a Monday, the start of a new week.

Thank you so much for all of your help. Has helped me tremendously!

Lisa

 

[shamimarita]

Hello Lisa,

Thank you for your kind words, and I’m glad to hear the tracker has been helpful so far! I’ve reviewed your latest concerns. Here’s a breakdown and solution for each point:

1. Updating for Years beyond 2025 (March 3rd falling on a Monday):
You're absolutely right, the sheet was originally built around a year pattern where March 3rd starts on a Monday. If you want to extend the tracker for future years, you can:

• Use Excel’s WEEKDAY(DATE(year, 3, 3)) function to check when March 3 falls on a Monday (it returns 2 for Monday).
• You can adjust the year dynamically by updating the year dropdown and formulas that use DATE() with a new year reference.
• For 2031, for instance, March 3 will again be a Monday — so you can shift the tracker forward by updating year-based formulas.

Let us know if you want this made dynamic via dropdown!

2. Some cells not calculating (highlighted areas):
This is likely due to:

• Deleted rows/columns: Some references might have been broken.
• Manual overwrites: Cells with hardcoded values may override the formula.
• Auto-calculation off: Please ensure Excel is set to Formulas → Calculation Options → Automatic.

You can select affected cells, press Ctrl + (to toggle formula view), and check if the formulas are intact.

3. #REF errors (blue highlights):
A #REF! error means a formula is pointing to a deleted cell or sheet.

To fix:

• Click on the cell and examine the formula — you’ll likely see something like ='SheetX'!#REF!.
• You can restore the broken link or replace the reference with a valid one using the correct sheet and cell/range.

If the entire row has this issue, it might stem from a copied formula from a missing sheet.

4. Week 0 showing 'xx' for March and excluding March 31st:
Excellent observation. The issue happens because:

• Week 0 is meant to show the first week overlapping February/March, but March 2025 starts on Saturday, so Excel cannot find a full week starting from Monday.
• Thus, Week 0 is treated as empty, and the tracker moves Week 1 to start from the next full Monday (March 3rd).
• Because March 31st is a Monday, and your formula depends on complete weeks from the month, it falls into Week 0 for April — hence March doesn’t catch it.

To fix:

• Modify the formula logic to include partial weeks if the month starts or ends midweek, or
• Manually assign March 31st to Week 5 of March if that better fits your reporting logic.