I have a spreadsheet for my products, those products change costs from the manufacturers on a regular basis and if it happened I will have to change my sales price as well, please how can I make it dynamic. Already I have a separate sheet for all the product prices, all I want is to just be able to return the current changed prices without affecting my previous sales.
I have attached an Excel project to clarified my question. On the spreadsheet please, kindly click on the sales sheet and sell out some products, then try to go to the price list and change the price and the effective date then return to the sales sheet and see the effect.
I want if any of my product prices changed it should only be effective on the current sales, not the formal. Kindly help
Nothing attached so it is difficult to answer the question without seeing the layout and formulas, but i would say that you will need to copy> pastespecial values on your formulas just before the price changes.
That could easily be done manually depending on the size of the work sheet, but it might make it more reliable to have a macro do it and even remind you when the price changes.
Purfleet
Hello,
As Purfleet hints, you should go for a VBA solution to calculate the sales price, the result should be put in the sheet as numbers. In such you can change the price without interfering historic sales data.
Br,
Anders
