I´m trying to create a spreadsheet to manage a business, in particular I want to use it to register the sales of the day. The problem i´m having in particular is that I need it to copy the price of the product sold from the prices sheet, to the sales sheet, but I need that value to remain the same even if later I modified the price.
I have managed to copy the value of the corresponding product, but whenever I update the price, the old sales change to.
To put it simple I need a script that whenever a cell from column "Product" in the sheet "Sales" is modified, applies a formula that searches in column "Products" (from sheet Products, within the same spreadsheet) for the corresponding product name, and then copies the value column "prices" from the sheet Products, to sales.
So when I need to change the prices, my old sales retain their original value. I have limited experience with scripts, I tried to make one with OnEdit, but can't make it work.
This is the document in question: https://docs.google.com/spreadsheets/d/18GHXoF9q8qe57o2YrKzLxCH9HlNV9SpF9fXYNPKq2N4/edit#gid=622868548
Best Answer
I do not know how to solve this problem without scripts, but there is a simple work around. When you update prices, you can drag them elsewhere in your sheet (e.g. below, in the form of a price record). The values on the 'Sales' sheet will stay linked to the original price, and the formula you enter for sales will be the same.
If you choose this work around, I would recommend creating a script that automatically moves your prices so that you don't have to manually drag things around and so that the newest prices appear closer to the top. There are many ways to make that type of script, but here's one not so elegant, but simple way:
Using a script may be more trouble than it's worth for this type of problem.
If for some reason (I can't imagine why) you want to have one cell that adds old prices and new prices, then you need a more advanced script. It would be easier to make one cell associated with each price, and then add them in the final cell.