Customer Price Lists

[DDH]

I produce Custom price lists. I have several tables; customer invoices, material prices Gross, material prices net, Customer material prices gross, Customer material prices net. the customer invoices list the materials they purchase. i use the following formula to look in each pricing table to find the correct price. then i use a pivot table to layout the price list. No totals or analysis needed, just columns for product type, sku and description, old price, new price, Increase Dollars, and Increase %. I've wondered if there is a better way to do this? thanks for any insights.

=IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(Y78347,customerNet!L:M,2,FALSE),VLOOKUP(Y78347,customerGross!L:M,2,FALSE)),VLOOKUP(Q78347,MaterialNet!E:J,6,FALSE)),VLOOKUP(Q78347,MaterialGross!E:J,6,FALSE)),"No Price")

 

[steve the fish]

You are happy to have either a customers net price or gross price or a materials net price or gross price? How do you know which one you have? Not sure how you produce price lists from that but you havent given much to work on.

 

[DDH]

You are happy to have either a customers net price or gross price or a materials net price or gross price? How do you know which one you have? Not sure how you produce price lists from that but you havent given much to work on.

Hi Steve,
all part #s have either a material gross or material net price. many, but not all part#s also have either a Customer gross or customer net price for various customers.
the two customer price tables have a column containing a concatenation formula with the customer acc # and part # followed by a column containing the price.

customerPart	Customer Price
100083271082814	528.00​

the invoice table contains a column with customerPart followed by a Price Column with the nested iferror functions the looks first in customerNet, then customerGross, then materialNet, and finally in materialGross.

customerPart	Price
100083271082814	528.00

the pivot table pulls from the Invoice Table. I change the Pivot table filter one at a time and then save each as a pdf and excel file. I am always looking for better ways to improve/speed up the process. I recently learned about the Pivot table >options>show report filter pages. which will create each pivot table price list to separate sheets all at one time.

 

[steve the fish]

You still dont really say how you know if you are picking up a gross price or a net price. What is a gross price? Inc tax or something?

 

[DDH]

You still dont really say how you know if you are picking up a gross price or a net price. What is a gross price? Inc tax or something?

Gross price is allowed customer discount, Net price is not allowed discount. if there is a Net price available, then it should be used first. if there is a Customer price then it should be used before the Material price. Hence, iferror formula looks for customerNet first, if none then looks for customerGross, if none then looks for materialNet, if none then looks for materialGross, if none then outputs "NO PRICE". hope this makes more sense. thank you for your patience.