I have a order entry system I composed in excel that works great except for one fatal flaw.
I have a products list with model numbers, descriptions, and prices
I have a customers orders sheet that takes the customers info; ie name , address, phone number,quantity of item purchased and , product purchased ect.
I have a purchase order
I have a sales Receipt
I enter the customers info in the customers orders sheet 1 customer per row each with its own unique order number
I then go to the sales receipt sheet and enter the order number, I have programmed the sales receipt sheet to lookup all
the information it needs from either the customers order sheet or the products sheet and after I enter the order
number everything gets filled in automatically, same thing for the purchase order sheet
The one flaw I have is that when a manufacturer changes their prices or I change my prices, the product model number doesnt change, and all my formulas are using the model number of the product to key on.
Here is what happens. Customer buys a hammer model number 100 on Jan 1, 2006, the cost of the hammer to myself is $5.00 and the customers price is $10. On June of 2006 the manufacturer
raises the price of the hammer model# 100 to $6.00, and I in turn raise the price to $12.00, the pricing has changed but not the model number of the hammer. I put the changed price
into the products sheet and prepare the order for a customer who buys the hammer in July and the sales receipt returns the correct new price and so does the purchase order, however
the 24 hammers that were previously sold in months prior to the June markup are now affected and return the higher marked up price in their respective rows.
I have not been able to resolve this problem since all my sheets key on the model number of the product being purchased to draw this info from. The only thing I have been able to come up with is
that when prices for a product change a new products sheet is made call it PRODUCTS2 and then all formulas from that date forward are set to key on the new PRODUCTS2 sheet instead of the originalProducts sheet. This approach actually works but I now have multiple manufacturers with lots of products and each manufacturer makes price changes at different times of the yearwhich means I could havee dozens of different ProDUCTS sheets and having to change lots of formulas. I would greatly appreciate someone pointing me in the right direction towards a solution. Thank you for your help.
I have a products list with model numbers, descriptions, and prices
I have a customers orders sheet that takes the customers info; ie name , address, phone number,quantity of item purchased and , product purchased ect.
I have a purchase order
I have a sales Receipt
I enter the customers info in the customers orders sheet 1 customer per row each with its own unique order number
I then go to the sales receipt sheet and enter the order number, I have programmed the sales receipt sheet to lookup all
the information it needs from either the customers order sheet or the products sheet and after I enter the order
number everything gets filled in automatically, same thing for the purchase order sheet
The one flaw I have is that when a manufacturer changes their prices or I change my prices, the product model number doesnt change, and all my formulas are using the model number of the product to key on.
Here is what happens. Customer buys a hammer model number 100 on Jan 1, 2006, the cost of the hammer to myself is $5.00 and the customers price is $10. On June of 2006 the manufacturer
raises the price of the hammer model# 100 to $6.00, and I in turn raise the price to $12.00, the pricing has changed but not the model number of the hammer. I put the changed price
into the products sheet and prepare the order for a customer who buys the hammer in July and the sales receipt returns the correct new price and so does the purchase order, however
the 24 hammers that were previously sold in months prior to the June markup are now affected and return the higher marked up price in their respective rows.
I have not been able to resolve this problem since all my sheets key on the model number of the product being purchased to draw this info from. The only thing I have been able to come up with is
that when prices for a product change a new products sheet is made call it PRODUCTS2 and then all formulas from that date forward are set to key on the new PRODUCTS2 sheet instead of the originalProducts sheet. This approach actually works but I now have multiple manufacturers with lots of products and each manufacturer makes price changes at different times of the yearwhich means I could havee dozens of different ProDUCTS sheets and having to change lots of formulas. I would greatly appreciate someone pointing me in the right direction towards a solution. Thank you for your help.
