Multiple Sorts Off a Weekly Master?

[Lunch Lady]

My questions are all related to student and staff orders for a hot lunch.

Each month every student at my school is given a one-page calendar to they may order a lunch. Out of 100-some students, usually about 75 will return an order that may be for one day, or for every day in the upcoming month.

• There are 14 separate classes from Pre-K to eighth grade.
• The menu is the same for each day of the week:

Monday: chicken nuggets or chicken sandwich
Tuesday: soft taco, crunchy taco, or burrito
Wednesday*: hamburger, cheeseburger, grilled cheese
Thursday: barbeque sandwich
Friday: cheese or pepperoni pizza

*If a Pre-K student orders a burger it needs to ordered in a junior size with the vendor.

Once the monthly order for each student is recorded I would like to be able to do the following:
1. Print a weekly list for each teacher which lists every student receiving a hot lunch and what they ordered for each day of the week.
2. Print a daily list for the lunch server which lists the student class, name, and lunch item they ordered. Ideally this would be grouped into the three separate lunch times: Pre-K, then K-4th, then 5th-8th.
3. Total the orders for each day of the week by item so a monthly order may be placed for each vendor.
4. Print a weekly list with daily totals of orders, with the totals grouped by class type: Pre-K, K-4th, 5th-8th. At the beginning of each week fruit and vegetables are purchased, and it helps to not only know how many servings are needed that week, but how many servings each day, and then how many of those servings are for the youngest students on up.

I currently have a weekly master like this:
Column A: student or staff
Column B: numerical designation for class (1-14)
Column C: class name (PK 2, PK 3, ... Seventh, Eighth)
Column D: student or staff name
Columns E and F: two Monday choices
Columns G, H and I: three Tuesday choices
Columns J, K, and L: three Wednesday choices
Columns M: Thursday choice
Columns N and O: two Friday choices

I quickly realized that if I sort off of this master for one day, copy it to another sheet, I cannot return the master to its original format to sort a second day. Inter-dependence issue I'm guessing?

I can see if I copy Columns A-D to the five sheets following the master, and use one for each day of the week, I could sort the
sheet. Correct? How would I enter an order on the master then have it populate in the correct cell on the sheet so I only have to enter an order once?

At this point I am so tangled up in trying to handle the administrative part of this lunch business efficiently I don't know if my approach/setup is worthwhile. In addition, I have never really done any sorting, filtering, referencing, etc, in Excel, so I'm having trouble looking for help.

Any input would be greatly appreciated.

Thank you,
-- LL

 

[Tyron]

Hey Lunch Lady,

Just to update you on the staff situation. For now to simplify things we will just indicate that all non-students are staff. So if they are from the office or a teacher's aide, or enrichment we will just consider them "Staff".

In the future if something needs to be tweaked on this it will be easier for you that way.

later

Ty

 

[Lunch Lady]

A vendor order would look something like this:

MONDAY - 10/6
Nugget Packs: 40
Sandwiches: 10

TUESDAY - 10/7
Crunchy Taco: 23
Soft Taco: 17
Burrito: 9

WEDNESDAY - 10/8
Jr Hamburger: 0
Jr Cheeseburger: 2
Hamburger: 3
Cheeseburger: 12
Grilled Cheese: 19

THURSDAY - 10/9
BBQ Sandwich: 11

FRIDAY - 10/10
Cheese Pizza Orders: 24
Pepperoni Pizza Orders: 8

For what it's worth:

Vendor orders are each 4-5 weeks worth of orders.

For the Wednesday order: any hamburger or cheeseburger order for a class younger than Kinder needs to translate to an order for a junior size burger.

For the Friday order: these orders need to be translated to whole pies.

 

[Tyron]

Hey Lunch Lady,

Okay this doesn't sound like a problem.

Friday Question:
How many slices does a student get for Friday?
How many slices = 1 pie?

 

[Lunch Lady]

Ty,

There are 10 slices per pie. PK students can have 2 slices, kinder through 8th can have 3 slices.

LL

 

[Tyron]

Hey Lunch Lady,
On the Pizzas you have indicated that a student "can" have a certain number of slices. Does this mean that you give them 1 and they have to come back for the other? Or does this mean you put the fully alloted slices on the tray when you give them lunch?

Also, when you place your orders do you order the max amount of slices dependant on the student/staff member ( I assume that if it is staff then they get 3 slices as well whether they are in a PK class or higher) and just have left over at the end of Friday if the kids/staff don't come back for their additionally alotted slices?

Another question:
At what time do the students/staff get on the list? Is it anytime or are you aware of the people before the month starts for all 4 or 5 weeks?

Sorry to be such a pain, but felt it best when calculating the vendor quantities.

later

Ty

 

[Tyron]

Hey Lunch Lady,

Forgot to ask in addition to the above questions in the previous post:

Is K Prep considered above PK* or part of PK*? There always has to be one little straggling question. lol.

Later

Ty

 

[Lunch Lady]

Ty,

In response to the straggler:

K Prep is part of PK.
Breakdown is:
Preschool, or PK = PK2, PK3, PK3-4, PK F, and K Prep.
Lower School = Kinder through 4th
Upper School = 5th through 8th

LL