Excel Challenge: Copy and pasting sheet1 to sheet2

[Lita]

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 qty>0 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?

 

[Satti Charvak]

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.

 

[Max]

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

1- copy all the rows where the qty>0 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 D211
The last 2 "Total" rows contain simple formulas calculating Total Qty/Items
"Total Qty" in D12: =SUM(D211)
"Total Items" in D13: =COUNTIF(D211,"<>")

Then in Order Summary,
In A2: =IF('Order Catalog'!D2="","",IF('Order Catalog'!D2>0,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
---

 

[Max]

Missing step:

.. in Order Summary,
In A2: =IF('Order Catalog'!D2="","",IF('Order Catalog'!D2>0,ROW()))

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