H
Herman56
I got a problem returning data from one worksheet to another: I have
(amongst others) two worksheets, named 'pricelist' and 'products'.
The 'pricelist' worksheet consists of product names (column B),
'tag'-columns for all the packings, bottles or boxes (C-L), prices
(M/N) and packings per box. All tags are made by an 'x', while for each
packing per product one line is used. This way there are multiple
entries for each product.
The 'product' woksheet consists of product names (column B),
characteristics of the products (C-AX), and then for each packing-type
a tag-column (AY, BH, a.s.o.), netto price per liter/kilo (AZ), taxes
(BA), bruto price per liter/kilo (BB), netto price/packing (BC), bruto
price/packing (BD), packings/box (BE), netto price/box (BF) and bruto
price/box (BG).
In the pricelist-sheet you add the articles per categorie, and then you
tag the packings, one line for each packing per product. Then in the
product-sheet all of these tags should show up, and a netto price per
kilo/liter will be entered there, after which the other prices are
developed by formula.
I got stuck twice: when I have added all the products and their
packings in the pricelist, only the first appearance of every product
will be checked in the product-sheet, which will always be the smallest
packing. I have used this formula:
=IF(ISNA(VLOOKUP($B3;pricelist!$B$3:$L$250;2;FALSE));"";
IF(VLOOKUP($B3;pricelist!$B$3:$L$250;2;FALSE)="";"";
VLOOKUP($B3;pricelist!$B$3:$L$250;2;FALSE)))
And yes, I realized I had forgotten that VLOOKUP is only finding the
first item in the list... :-( But how can I do this in the proper way,
making each x-tag show up in the proper place in the product-sheet?
And then I encountered my second problem: there are (at this moment) 9
different packings used, and there might be added a few more in the
future. I had expected to be able to use an IF-formula, like the
following:
=IF(C3="x";products!BC3;IF(D3="x";products!BL3;IF(E3="x";products!BU3;
IF(F3="x";products!CD3;IF(G3="x";products!CM3;IF(H3="x";products!CV3;
IF(I3="x";products!DE3;IF(J3="x";products!DN3;""))))))))
But since I can only use 7 nested IFs, I cannot use this. Neither can I
use a SUMPRODUCT-formula, since I do not need to have prices added, I
just need to get the proper netto and bruto prices as well as the
proper amount of packings per box 'transferred' from the product-sheet
into the pricelist. How will I be able to get this right? Is there
someone around who can bring the light to me, please?
Paul
(amongst others) two worksheets, named 'pricelist' and 'products'.
The 'pricelist' worksheet consists of product names (column B),
'tag'-columns for all the packings, bottles or boxes (C-L), prices
(M/N) and packings per box. All tags are made by an 'x', while for each
packing per product one line is used. This way there are multiple
entries for each product.
The 'product' woksheet consists of product names (column B),
characteristics of the products (C-AX), and then for each packing-type
a tag-column (AY, BH, a.s.o.), netto price per liter/kilo (AZ), taxes
(BA), bruto price per liter/kilo (BB), netto price/packing (BC), bruto
price/packing (BD), packings/box (BE), netto price/box (BF) and bruto
price/box (BG).
In the pricelist-sheet you add the articles per categorie, and then you
tag the packings, one line for each packing per product. Then in the
product-sheet all of these tags should show up, and a netto price per
kilo/liter will be entered there, after which the other prices are
developed by formula.
I got stuck twice: when I have added all the products and their
packings in the pricelist, only the first appearance of every product
will be checked in the product-sheet, which will always be the smallest
packing. I have used this formula:
=IF(ISNA(VLOOKUP($B3;pricelist!$B$3:$L$250;2;FALSE));"";
IF(VLOOKUP($B3;pricelist!$B$3:$L$250;2;FALSE)="";"";
VLOOKUP($B3;pricelist!$B$3:$L$250;2;FALSE)))
And yes, I realized I had forgotten that VLOOKUP is only finding the
first item in the list... :-( But how can I do this in the proper way,
making each x-tag show up in the proper place in the product-sheet?
And then I encountered my second problem: there are (at this moment) 9
different packings used, and there might be added a few more in the
future. I had expected to be able to use an IF-formula, like the
following:
=IF(C3="x";products!BC3;IF(D3="x";products!BL3;IF(E3="x";products!BU3;
IF(F3="x";products!CD3;IF(G3="x";products!CM3;IF(H3="x";products!CV3;
IF(I3="x";products!DE3;IF(J3="x";products!DN3;""))))))))
But since I can only use 7 nested IFs, I cannot use this. Neither can I
use a SUMPRODUCT-formula, since I do not need to have prices added, I
just need to get the proper netto and bruto prices as well as the
proper amount of packings per box 'transferred' from the product-sheet
into the pricelist. How will I be able to get this right? Is there
someone around who can bring the light to me, please?
Paul