Order Sheet for Supplies for my Special Education Class

[Na-Dude]

Hello Everyone,

First of all, THANK YOU for helping.

I am a high school teacher of a special education class. I've been out in charge of ordering supplies for our department. Our school has six classes in the special education department. Each class has to put in an order. I was given a photocopy of a photocopy of a photocopy, not sure how many times it was copied over the years. I made several copies and handed them out to each class. Each teacher highlighted what they wanted and handed the sheets back to me. it took me almost a week to go through the thousands of items to type up which each teacher asked for. I then created an excel sheet and entered the orders from each class. I just realized I didn't enter the class number or the teacher's name to the excel sheet. I have no idea what each teacher/class ordered and the totals for the teach/class. I will not have to go over the thousands to freaks line items to figure out what each teacher ordered.

Can someone please help me simplify this process. I am not a computer teacher and have minimal experience with excel.
I am looking for:
01. An excel sheet with different tabs for each class with teacher name and or class number
02. a tab that shows the totals of all teacher's orders added up. for example, if three teacher order 2 boxes each of blue pens, this tab will say "blue pens = 6" and the totals. This tab will be printed and used to order the items.
03. a tab that counts how many items each teacher orders and the total for each teacher/class
04. Any other important information you can thing of would be appreciated.

I am not sure if I am asking for too much.

Thank you everyone for whatever idea and help you can provide.

[6StringJazzer]

You provided a sheet with about 100 items to select, and you said there are 6 classes. How did you end up with thousands of items?

I assume you want to give the teachers a list of pick from, the same way your photocopy order forms worked.

01. An excel sheet with different tabs for each class with teacher name and or class number

Do you have a list of teacher names and/or class numbers? Do not provide the teachers' name here; I am just asking if you have it.

03. a tab that counts how many items each teacher orders and the total for each teacher/class

Wouldn't this be the same as what you described in item 01?

Do you need to print these out on paper at any point?

Here's a model you can use. It has three classes. To add classes, copy a Class sheet and just make sure it ends up between the tabs labeled First and Last. This are bookends to allow the use of the 3D SUM reference in column F of the main order sheet.

[Na-Dude]

There are 28 sheets with about 40 lines products per sheet (so not thousands, just seems like it when I am going over
6 classes who highlighted items from their own set of 28 sheets). The excel sheet attached is what created to submit
to the ordering department.

I would love to give each teacher an option to pick from but to enter over 1,000 items will take me a long time; this is
something I will do over few months.

Class Names:
01. PLP1
02. PLP2
03. PLP3
04. SSP1
05. SSP2
06. DEV1

Yes, I do not to print the final copy. I printed the attached sheet to submit. This should only print the items ordered. If no one ordered, it should not display on this sheet. Sorted by "DDSB PROD. CODE"

I need one tab that calculates each class's totals, and all added up together. For example: (PLP1 - 21 items - $50.34 total) (PLP2 - 10 items - 100 total) All total items orders 31 and $154.34 total

Thank you for your assistance...

[Na-Dude]

I have updated the workbook. I need to get the total amount of supplies orders in the first tab and only items that were selected to appear in the print tab sorted by the DDSB tab. I will enter all supplies in the sheet.

[torachan]

Are you allowed to use VBA/macros ?
Sheet "Totals" what are you expecting to see in column 'C' ?
Although the details/quantities in all sheets appear to be the same, I presume in fact that quantities on each sheet will differ and some will be zero ???
Are you physically/manually entering the data or can the class individual enter the quantities directly into the app cutting out the paper stage (or is that expecting too much).

[Na-Dude]

Are you allowed to use VBA/macros ?
I am not sure what macros are, but if it will help then yes.

Sheet "Totals" what are you expecting to see in column 'C' ?
The total number of supplies ordered. There is a list of over 1,000 items. Just a number of a total of supplies a class ordered. For example, PLP1 ordered 50 supplies.

Although the details/quantities in all sheets appear to be the same, I presume in fact that quantities on each sheet will differ and some will be zero ???
There are over 1,000 items in the supply list. I will enter them all into the excel sheet and copy them to all classes. Every class will pick what they want but entering the quantity on their tab, this then should hopefully add to the print tab and update the totals tab.

Are you physically/manually entering the data or can the class individual enter the quantities directly into the app cutting out the paper stage (or is that expecting too much).
Once I add all the items into each class. The print tab will be empty. When each class picks the quantity of what they want, only then that item should appear in the print tab sorted by the “DDSB PROD CODE”


Here is the scenario:

I want to make it easy for all classes to pick their supplies (over 1,000-line items). The AR tab will be the primary tab. I will add all the line items there and link all the sheet to the same information. This will make it easy in the future to add more Items and have them copied over to all classes. The only item each class should be able to add is the quantity.

The “Print for Ordering” tab will be updated, sorted by “DDSB PROD CODE” alphabet. Items should only show up in this tab when a class has chosen the supply. Generally we only have around 200 items list that we print.

The totals tab is a quick snapshot of the order.

[torachan]

For example, PLP1 ordered 50 supplies.

Am I missing something - I do not see how you tell that from the attached 'PLP1' sheet ???
Will each classes sheet have all 1,000 line items listed with quantities just in the (200 or so) boxes they require - leaving 800+ rows with zero quantities ????

[Na-Dude]

Currently we are all given 30 sheets of paper with about 40 line items on them. Each class takes a highlighter and highlights what they want and write the number of quantity. I then have to manually typed each item each class requested. I ended up with all classes 30 pages each, I had to figure out the duplicates and add them accordingly. That is what you currently see.

I will type in all the information for all 1,000 plus line items in the AR sheet. So, all teacher will have all 1,000 plus items with zero quantity. When they change the quantity from zero to three or four it will update the order sheet adding to what the other classes ordered.

Each class is given their own 30 pages. You can imagine how much time it took me to figure out if there are duplicates and then put it all together. The lady who has been doing this for years has retired but she made it very difficult for her self.

I apologize if I am unable to explain properly...

[AliGW]

Is it just six classes in total, or are there potentially more? You have it seems provided identical copies of orders for each class - this is not terribly realistic!

I think the easiest way of doing this may well be by using PowerQuery. Have a look at the attached and let us know if it does what you want. If you need to add more classes, just hit Refresh All on the Data ribbon to update the master order for printing.

If it's what you want, I can talk you through its creation.

[Na-Dude]

Classes go up and down every couple of years, this year we had six classes and I think next year another DEV (development class is being added). I copied the excel sheet I created from all the classes as an example. All class tabs will have over 1,000 plus line items (eventually I will manually input them all); the teacher from each class will only put in the quantity of each item their class needs.

Once I have entered all the line items into the system, will it only load the ones items with the quantity value entered int the "print for ordering" tab?
Is there a way to show the totals information on the totals tab? I would like to have a dashboard type information on this tab, like top five most expensive products by class, the cost for the products, and top five over all most expensive products ordered.
Do I just add another tab for a new class and the query will automatically pick it up?

I will be sharing the worksheet over the school network with all the teachers.

Ali, I really appreciate you helping me. THANK YOU from the bottom of my heart. I am a special education teacher working with kids with special needs and have minimal experience with computers.

[AliGW]

I was a teacher, too.

We can make sure that it filters out any lines with nothing in the quantity column - that's easy to add.

Do I just add another tab for a new class and the query will automatically pick it up?

If you add a new class, make sure you click Refresh All on the Data tab and it will be added to the master copy.

Is there a way to show the totals information on the totals tab?

That's just a SUMIFS - that's not part of this query, but I can show you how.

I would like to have a dashboard type information on this tab, like top five most expensive products by class, the cost for the products, and top five over all most expensive products ordered.

This is beyond the scope of this thread - it's a whole new question (that will require a suitable title) - I suggest you complete this thread first and then open a new one about your dashboard requirements.

[azumi]

For numbering put this on A2 on sheet PLP1:

=IF(B2="","",COUNTA(B2:$B$2))

For Product Desciption put on B2 on sheet PLP1 then copied down:

=IFERROR(INDEX(AR!B$2:B$10000,AGGREGATE(15,6,(ROW(AR!B$2:B$10000)-MIN(ROW(AR!B$2:B$10000))+1)/(AR!B$2:B$10000<>"")*(AR!$A$2:$A$10000<>""),ROW(A1))),"")

For Vendor Name and so on put on C2 on sheet PLP1 then copied down and cross:

=IFERROR(INDEX(AR!C$2:C$10000,MATCH($B2,AR!$B$2:$B$10000,0)),"")

then copy all sheet PLP1 then paste to PLP2 and so on

For Total Suplies Ordered put on C5 on sheet Total then copied down:

=SUMPRODUCT(SUMIF(INDIRECT(B5&"!F2:F10000"),"<>"))

For Total Cost on D5 on sheet Total then copied down:

=SUMPRODUCT(SUMIFS(INDIRECT(B5&"!G2:G10000");INDIRECT(B5&"!A2:A10000");"<>"))

[AliGW]

In C5 copied down:

=SUM(INDIRECT("'"&B5&"'!F2:F1310"))

In D5 copied down:

=INDIRECT("'"&B5&"'!G1311")

The query has had an empty row filter added (filtering out anything with no value in the QTY column).

If this is what you want, then please mark this thread as solved and open a new one for your dashboard question.

[AliGW]

@azumi

I think that's the complete opposite of what's required! It's the printing sheet that needs creating, not the individual class sheets.

[Na-Dude]

Hello Ali,

I emptied all the QTY field in all the tabs except one (DEV1), when I click refresh all the QTY field in the "Print for Ordering) tab does not refresh, it keeps adding on the the quantity.

Am I doing something wrong?

[AliGW]

No - it's just loading the order from DEV1 - look at the totals sheet.

[Na-Dude]

Yes ma'am, it is updating the totals tab, but when I click on the "Refresh All under Data" in the "Print for Ordering" tab it keeps adding to the numbers already in the QTY column. It won't reset and update the current values from the other tabs. Unless this is a limitation?

[torachan]

I blinked and the subject raced ahead.
However as I had already partially started I thought I would post my approach.
In the attached there is a master list (blank quantities & totals) - this is distributed to each class - they manually highlight and fill in totals - you now have the task of entering totals to class lists.
This is the stage that I first concentrated on - with the use of the UserForm you select the class from the dropdown (you only do that once for each session) then it is a simple entry of code number and quantity repeated until finished (this builds the class sheet in the background and only records relevant data (not a pointless record of zero quantity records) this is the stage that the attached app is at, at this moment.
The next stages would be formation of the amalgamated quantities, followed by a breakdown of orders to individual suppliers if this is a path you wish to pursue.

[AliGW]

It won't reset and update the current values from the other tabs. Unless this is a limitation?

Try this. One of the table names had changed - it should work now.

[Na-Dude]

@AliGW

I think you uploaded the wrong worksheet...

[Na-Dude]

@torachan

It looks great. When I enter the information in the "SHOW FORM" it is only entered appears in the associated class, how can it be added to the "ORDER" tab?

Can we show the Total Quantity and the Total Cost in the "Totals" tab.
Overall, ten most common items orders, qty ordered, total cost
Overall, top ten highest costing orders, qty ordered, total cost
Overall, top ten lowest items ordered, qty ordered, total cost.
by class top five highest costing orders, qty ordered, total cost
by class top five lowest items ordered, qty ordered, total cost.

Am I able to add more line items in the "MASTER TAB"?


Thanks

[AliGW]

Sorry! Attached here.

[AliGW]

Can we show the Total Quantity and the Total Cost in the "Totals" tab. ...

As I said before, this is beyond the scope of THIS thread. It will require a new thread with the requirements stated and a suitable title.

[AliGW]

This version uses PQ to generate the totals table in the master tab.

It requires you to name your individual class tables in the format Class_classname (e.g. Class_DEV1) - you do this in the Name Manager dialog on the Formulas ribbon.

You need to decide whether to take this approach or the VBA approach.

Once you have decided, you need to mark this thread as SOLVED and open a new, suitably titled thread for the analysis part of the project.

[torachan]

@Na-Dude, the answer to all the questions is 'Yes'.
I did not develop my approach any further once I saw a PQ solution was been offered as many people prefer a non-VBA/macro approach as they find it more understandable (although there is nothing mystical about VBA).
I will await your decision/reply to @AliGW's post#24. - you may find her approach more supportable overtime if you have limited VBA knowledge.

[Na-Dude]

Thank you @torachan. I will use Ali's worksheet. I appreciate your help.

[Na-Dude]

@AliGW

Couple of things I am unable to get working.
01. I created a tab called "New Class". In the totals tab it was showing up as DEV15. When I change this "DEV15" to "New Class" and refresh Data, it reverts to "DEV15".
02. I can change the column width in the "Print for Order" tab but when I refresh Data, it reverts.

Please don't be mad, I changed visual formatting for the classes, I linked all cells to AR, so, when I add more line items in the "AR" tab, all classes will be updated
I removed the total "$ -" in the "Total" column (=IF(F2="","",E2*F2)) in all classes. Plus, minor other cosmetic changes. This caused the "Print for Ordering" and "Totals" tab to break.
In the "Totals" tab in is not adding up properly and in the "Print for Ordering" tab only one line is appearing.

[AliGW]

Oh, dear - it sounds like you've had a bit of a field day! I will see if I can have a look tomorrow, but am away for the wwekend and may not have time to resolve any issues. It might have to wait until Monday, I'm afraid.

[AliGW]

Morning!

02. I can change the column width in the "Print for Order" tab but when I refresh Data, it reverts.

That's just something you'll have to live with, I'm afraid. But for me, they automatically adjust to the widest item on refresh, so this shouldn't be an issue.

01. I created a tab called "New Class". In the totals tab it was showing up as DEV15. When I change this "DEV15" to "New Class" and refresh Data, it reverts to "DEV15".

As I explained before, when you create a new class tab, you need to name the table yourself. To do this, you go to Name Manager on the Formulas ribbon and find the class - rename it to suit with the Class_ prefix (which it will have by default). This ensures that the correct tables are filtered for the print order and totals tables. Do NOT name it without the prefix!

Finally, I found the number 2 at the bottom of each table to the left of the total in QTY row 1231 - don't know why it was there, but I removed it in each case. The QTY column is used for filtering, so the totals row must remain clear otherwise it becomes a part of the order and totals tables!

So it's all working correctly now.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. Start a new, suitable titled thread for the dashboard questions.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Na-Dude]

Thank you so much AliGW. You are amazing.

[AliGW]

You’re welcome.

[torachan]

thanks for the feedback and added rep point - at least it gave you a brief insight that there are many methods within Excel to solve your problem.