Thanks, but i not sure I understand your freeze frame option, but I see where
it is going and not quite where i was going with this project! Thanks!

I am interested in the macro, but would rather have some mthod or function
that determines selected widgets, my case col G (I will be pulling several
fields from that row) and writing out to a 15 rowed Invoice, all the
totalling is already done, just trying to have a clean list of selected items
on the Invoice.

Tom

"OssieMac" wrote:

Hi,

You might like to try the following.

Use AutoFilter. I dont think it is the greatest way of creating invoices
but it works and if you want to keep a copy of the invoice then a macro could
be used to copy it to another worksheet or even another workbook. (It can
also be copied manually)

Place column headers on say row 11.
Click in cell A12 and freeze panes so that you now have rows 1 to 10 to
insert your own business details and customer details.
Insert all of your products and any other info re price and number ordered
etc and formula for calculated cost for each item in the table.
At bottom of list use SUBTOTAL function to get all the grand totals. (You
can leave 1 or 2 rows blank between the table of data and the subtotals.)
Lookup SUBTOTAL in help for all the math options of Sum, Count, Average etc.
Click somewhere in the table and set AutoFilter.
Now insert the number ordered against each item. (I think that this is
preferable to Y and it is specific on an invoice)
Click the dropdown on the number ordered and select Custom (or with xl2007
select Number filters) and set to Greater than 0 (Zero).

Hey presto you have your invoice ready to print. (The drop down arrows do
not print.)

It is quite easy to create a macro attached to a button on your worksheet to
toggle the AutoFilter between show all and greater than zero on number
ordered.

You will need to clear the number ordered data before creating a new
invoice. This could be included at the end of a copy to another worksheet
macro.

1. If you want the macros then reply but I need to know:
2. What column has the number ordered (counting from the left of the filter
column headers).
3. If you want the copies in another worksheet same workbook or in another
workbook.

--
Regards,

OssieMac


"thayes" wrote:

I have been racking my head around this and several suggestions were very
close, but I could not get them to work...

I have a user interactive excel session and want to dynamicly collect parts
list, create an order sheet and invoice.

I have a parts list?
Col_A Col_B
widgit A Y
widgit B
widgit C Y
widgit D

I know I need to build an array and index thru and remove the blanks and
nulls...

Desire result, somewhere on the same worksheet:
Order
widget A
widget D

That is it, pretty simple, but I have yet to figur this out.