[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!