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

 

[Smitty]

You can post workable screen shots with the MrExcel HTML Maker (you'll find a link in my sig).

As for the reports, I'd look at Pivot Tables (www.pivottable.com), which are a great way of automating things like this.

 

[Lunch Lady]

Smitty - can't PM you, says your box is full!

 

[Smitty]

Yup, got that, so I just cleaned it out.

 

[Tyron]

Hey Lunch Lady,

Looks good actually. I am going to try working on it later this afternoon. I have a question though. In the last section for the weekly shopping totals is there a reason why on Oct 6th there is a box around the 5-8th and no where else?

Addressing your indication that you want to have printable pages - That shouldn't be an issue unless you need the format to be specific which may require and extra worksheet or two at worst case scenario depending on that format you are looking for.

Will get back to you later this evening with any other questions if any and a sample.

later

Ty

 

[Tyron]

Hey Lunch Lady,

I am about half way done with my working copy. I just have a few questions.

1.)Will you be using the peoples last names as well?
2.)In the weekly order sample what is the significance of "NUMBER"
3.)Have you listed all of the different classes? PK 2 through 8 or did you leave out PK 1 or any others?
4.)The teacher weekly reference sheet lists all the students in a particular class correct? Or should there also be a spot for the staff in those classes as well?
5.)Is there any way that you could repost the Vendor List for me? It seems to have gotten a little messed up.

Thanks in advance

later

Ty

 

[Lunch Lady]

No reason for the box around those grades on October 6 -- just a copy/paste error on my part.

I don't picture how, once the workbook is set up to sort each week's info, a print area is determined. Also, some classes are very small, others have the maximum number of students. So again, I don't understand how the sorts for each class feed in to a printable page for each class and each page is easily printed.

 

[Tyron]

Hey Lunch Lady,

Well it isn't so much that it feeds into a print area so much as you are using the information to populate a seperate worksheet that will print in the format of your choosing.

So, what your first dilema is in printing is determining what is the most efficient and beneficial format is for your customer (volunteer servers). I have almost got the stuff set up for review. Probably after we work through any questions I may have we may have a working project for you by Saturday at the latest.
The latest questions I have are posted above. There are five just to iron out some details.

later

Ty

 

[Lunch Lady]

I hope my reply sequence hasn't confused things here. This second reply is to your questions from 9:05 today.

1. First and last names will be used.
2. Just answered this in a PM, but it was only an attempt to allow a numerical sort rather than create a custom sort and need to enter each class type.
3. Class list is complete as listed. No PK 1.
4. Teacher weekly reference sheets list all the students in that class. Should have occurred to me to include the staff for that class as well. Not too many staff order lunches, so it wouldn't be a big deal not to have it there, but if it was there it would serve as a confirmation to that staff member they will have a lunch as requested.
5. I'll work on the Vendor List.

Thanks Ty!

 

[Lunch Lady]

Hello again!

Your explanation about the print area makes sense, thank you.
Sometimes being a more visual thinker doesn't serve me.

LL

 

[Tyron]

Hey Lunch Lady,

No problem there. I know that in spreadsheets it is all about what the person using them likes/dislikes about seeing that is why visual usually ends up being a determining factor.

Also, I sent some other things to your PM, but your box is full. I forgot until you reach 50 posts your PMs are diminished to 10 received+sent. You may want to turn off in your settings to save a copy of what you sent to your sent folder and delete what you have in your sent folder. I always quote messages so you get to see what you sent me. I am going to get back to the work at hand and will give you until tomorrow to catch up on things. Thank you for the answers to the questions.

later

Ty