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?