I work in a school and have created a master supply list ordering
sheet(sheet1) for my teachers to use. It looks something like below.
Name Cat#. Qty
Pencil ABC
Pens BCD
Paper CDE
Since this is a long list and not all the cells in the qty column will be
filled, there will be blank cells. When the teacher is done they will hit a
"DONE" button on the spreadsheet and I would like the following to happen.
1- copy all the rows where the qty0 be listed on sheet2(summary sheet)
2- Be able to email the summary sheet as an attachement
3- Be able to have a time stamp and save and lock the worksheet.
4- on sheet1 last 2 rows are total values and would like this to be copied
on sheet2 also.

Is there anyone that knows of a formula to use to accomplish this?

Hi Lita,

The complete task can be accomplished in Excel, but the task is too complex
to be compled in one function or some simple macros. Your requirement
actually constitutes a small project.


--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"Lita" wrote:

I work in a school and have created a master supply list ordering
sheet(sheet1) for my teachers to use. It looks something like below.
Name Cat#. Qty
Pencil ABC
Pens BCD
Paper CDE
Since this is a long list and not all the cells in the qty column will be
filled, there will be blank cells. When the teacher is done they will hit a
"DONE" button on the spreadsheet and I would like the following to happen.
1- copy all the rows where the qty0 be listed on sheet2(summary sheet)
2- Be able to email the summary sheet as an attachement
3- Be able to have a time stamp and save and lock the worksheet.
4- on sheet1 last 2 rows are total values and would like this to be copied
on sheet2 also.

Is there anyone that knows of a formula to use to accomplish this?

Here's a simple, effective automated model (formulas driven)
to serve your core reqts 1 & 4:

1- copy all the rows where the qty0 be listed on sheet2(summary sheet)
4- on sheet1 last 2 rows are total values and would like this to be copied
on sheet2 also

Illustrated in this sample:
http://freefilehosting.net/download/42817
Automated Order Summary.xls

The Model:
Source catalog table is in sheet: Order Catalog, cols A to D,
where the key col = Qty (col D). with qty inputs within D2:D11
The last 2 "Total" rows contain simple formulas calculating Total Qty/Items
"Total Qty" in D12: =SUM(D2:D11)
"Total Items" in D13: =COUNTIF(D2:D11,"<")

Then in Order Summary,
In A2: =IF('Order Catalog'!D2="","",IF('Order Catalog'!D20,ROW()))
Leave A1 empty

In B2:
=IF(ROWS($1:1)COUNT($A:$A),"",INDEX('Order
Catalog'!A:A,SMALL($A:$A,ROWS($1:1))))
Copy B2 across to E2, fill down to E13. Minimize col A. Switch off zeros
display in the sheet via Tools Options View tab, uncheck Zero values OK

Then add a dash of CF for the 2 "Total" lines
Select cols B to E (with B1 active),
apply CF using "Formula Is" for Condition 1:
=OR($B1="Total Qty",$B1="Total Items")
Format to taste ok out

There you go. Orders filled in the Qty col in sheet: Order Catalog
will dynamically populate in Order Summary
with all lines neatly packed at the top,
inclusive the last 2 "Total" lines with distinctive conditional formatting
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"Lita" wrote:
I work in a school and have created a master supply list ordering
sheet(sheet1) for my teachers to use. It looks something like below.
Name Cat#. Qty
Pencil ABC
Pens BCD
Paper CDE
Since this is a long list and not all the cells in the qty column will be
filled, there will be blank cells. When the teacher is done they will hit a
"DONE" button on the spreadsheet and I would like the following to happen.
1- copy all the rows where the qty0 be listed on sheet2(summary sheet)
2- Be able to email the summary sheet as an attachement
3- Be able to have a time stamp and save and lock the worksheet.
4- on sheet1 last 2 rows are total values and would like this to be copied
on sheet2 also.

Is there anyone that knows of a formula to use to accomplish this?

Missing step:
.. in Order Summary,
In A2: =IF('Order Catalog'!D2="","",IF('Order Catalog'!D20,ROW()))

You need to copy A2 down to A13
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---