how to link workbook to invoice?

  • Thread starter Thread starter Falcon
  • Start date Start date
F

Falcon

I have created a workbook which has all of my inventory in it. In eac
row I have a cell in which I enter how many of that item I want. At th
end of the row it gives me the total price. The last sheet of th
workbook gives me the total of all the items picked.
Now what I want to be able to do is, when ever I enter how many of a
item I want, it would automaticly add it to an invoice. I have bee
trying to figure out how to link the invoice and workbook together bu
have had no success. If any one has any idea how to do this, I woul
realy appreciate the help.

Thanks, To
 
Hi Tom
you haven't provided that much information so the following is just a
good guees (at least I hope it is).
Some assumptions:
- Your inventory in in a sheet called 'inventory'
- column A contains your product name and column B your number of
picked items 8col. B is empty for non pickted items)
- on your invoice sheet you start the choosed item list in column A,
row 2 (row 1 could be a heading row)

Enter the following in A2 on your invoice sheet (entered as array
formula with CTRL+SHIFT+ENTER)
=INDEX('inventory'!$A$1:$A$100,SMALL(IF('inventory'!$B$1:$B$100<>"",ROW
(''inventory'!$B$1:$B$100),1000),ROW()-1))
and copy down

in cell B2 enter the array formula
=INDEX('inventory'!$B$1:$B$100,SMALL(IF('inventory'!$B$1:$B$100<>"",ROW
(''inventory'!$B$1:$B$100),1000),ROW()-1))
and copy down

you may hide the error (after no valid entry is returned anymore) with
a conditional format
 
More details: I have 3 sheets in my workbook. Each sheet contains
columns titled Item, price, quantity, and total price. When I enter a
quantity it gives me the total price. After I enter the quantity I
would also like it to add the Item, price, quantity, and total to a
invoice (seperate sheet to be printed out for cutomer). It looks like
this.

Item Price quantity total price
1------$--------------$
2---$1.00----1-----$1.00

Invoice would look like this:

Item Price Quantity Total price
2---$1.00----1-----$1.00

I hope this information makes it more clear what I am trying to do.

Tom
 
Back
Top