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!!

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" wrote:

> 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!!

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" wrote:

> 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" wrote:
>
> > 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!!

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" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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!!

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?

"Miguel Zapico" wrote:

> 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" wrote:
>
> > 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" wrote:
> >
> > > 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" wrote:
> > >
> > > > 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!!

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

"Paula_p" wrote:

> 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!!