I am using a simple formula to calculate the subtotal of an order:

=Products!$A$2 * Orders!B2

Since 11/13/2022, the product has been in sale and price was cut in half, and Products!A2 has been manually changed to 12.5. However, this not only affects the current and future orders but also affects the past orders which will generate a false total revenue.

I can think of some ways to deal with this issue. For example, to use another cell to store the sale price, and then put a condition in the formula to compare the order's date with 11/13/2022 and then get the correct price. But is this the best practice? Does Google sheet provide more native ways to do this? Like, once a cell's value is calculated, lock it so it won't change any more if any parameter of the formula changes?

CodePudding user response：

There is not quite as "native" a way as what you're describing.

In spreadsheet applications generally, values can be supplied by a formula or they can be "written" to the cell. You're talking about wanting both. That is somewhat possible with a google AppScript, but that is a bit beyond the scope of your question.

For example, you could have a script in place, that as soon as a value is placed in one column, the price in an adjacent column, being supplied by a formula, would immediately turn into a "hard" value written to the cell. But it is a difficult thing to write without any experience in AppScript and impossible to write without more information about the layout and structure of your sheet.

CodePudding user response：

click the cell you want to block. Right click it OR press home tab. In the Alignment group, click the small arrow to open the Format Cells popup window. On the Protection tab, select the Locked check box, and then click OK.