How do i generate a bill from an inventory list?

[Paula_p]

Hi everyone,
I have an invoice as one sheet and an inventory list as another sheet in the
same workbook. I would like to be able to select items from the inventory
list and have that info. (about 3 columns from 8) sent directly to the
invoice in order to generate a bill, however, the items are not necessarily
consecutive in the list. Is there a way to do this?
Thanks for any help!!

 

[Miguel Zapico]

Search the help for the VLOOKUP function, that may be useful in this case.
It also have a parameter to perform the lookup on non sorted lists.

Hope this helps,
Miguel.

 

[Paula_p]

Thank you, i found the vlookup formula and it is working very well, all i
need to do is type the product code from the inventory list in the invoice
sheet and the info. i need automatically upload itself. However, in using the
vlookup, if data is only present in the first few cells, the remaining ones
return a #value error. I found a way to correct this to return an empty
cells in the unit price column with the following:
=IF(ISNA(VLOOKUP(B21,Inventory!$B$3:$D$2000,3,FALSE)),"",VLOOKUP(B21,Inventory!$B$3:$D$2000,3,FALSE)).
My problem now is this, "sales tax on purchase" uses the sum product formula;
=sumproduct((a16:a32)*(d16:d32)*(e16:e32)), where column a has product
quantity, column d has unit price and column e has tax amount (which may or
may not apply). Howevwer, this generates a #value error after entering the
vlookup formula. I've tried to correct this with the following;
=if((d16:d32)="",0,sumproduct((a16:a32)*(d16:d32)*(e16:e32))), but i still
get a #value error. Can someone please tell me what i'm doing wrong.
Thanks.
Qty Code Description Unit price Tax
Total
3 2485 pencil $10
$30
5 0014 novel $15
2% $ 75


subtotal $105
sales tax on
purchase $1.50

total $106.50

 

[Miguel Zapico]

The SUMPRODUCT function doesn't need to have the product operator between the
arrays, try with
=SUMPRODUCT((A16:A32),(D1632),(E16:E32))

Miguel.

 

[Paula_p]

Thank you Miguel, everything in my invoice sheet works fine now.
In the inventory list where i store all my product information, including
quantity in stock, how do i get the quantity entered in the invoice to be
deducted from the current quantity on hand, and show the new figure. Say on
hand i have 10 pencils, and on the invoice i sold 3, is there a way to
automatically have the new quantity on hand updated in the inventory list?

 

[Mel]

hey i think that i need something like this do you think you can email me
yours just to see if mine would work?