0

Apologies if the title question is unclear, please feel free to suggest any better wordings of it.

I'd like to know if there is any sort of system in place where I can "freeze" the value in a cell after a certain period of time, when the cell references variables in another cell/sheet that may change. If A1 is supposed to be half of A2, and A2 is 100, then A1 should be 50. Say, for whatever reason, I now desire A1 to remain 50, even if I change A2 to 200. (I.E. after activating this cell "freeze", no matter what I change A2 to, A1 will always stay 50, even though the formula in A1 was once =A2/2.)

A practical example:

To further clarify my question, I can provide a practical example that is similar(ish) to what I am trying to achieve.

Say I have a budgeting spreadsheet that is broken up into months. The sheets are January, February, and so on. The first sheet, however, is a "Master" sheet that contains variables that each of the monthly sheets reference. This master sheet contains things like rent, insurance, the percent of each paycheck I wish to save, etc. The spreadsheets are balanced often such that they reflect my current finances, and I can refer to the spreadsheet when I need to see how I'm doing financially.

So, let's say in January, I log my expenses like so: An image of a Google Spreadsheet in January

I log my beginning balance and my expenses. I write in H2 that I begin January with $5000, and in A2:D that I pay rent, various assorted expenses, and get a paycheck. The sheet sums my total income and expenses, and calculates my ending balance with this information. The sheet also informs me that I should put away $100 into savings. Some of these cells are influenced by the Master sheet:

An image of a Master Google Spreadsheet

In January, cell C2 is simply =Master!B3. Furthermore, all of Column E contains cells with the formula =IF($D2="","",$D2*Master!$B$2) (reference D2 changes with each row), where column E is populated with a value if D has a value; if so, then it populates it with the value in D times Master!B2, otherwise, it remains "blank" (The empty character ""). Note that my ending balance in January is $3900.

Then, in February, I do the same:

An image of a Google Spreadsheet in February

This time, though, cell H2 is not a value I input, but simply =January!H6, as I expect the ending balance of January to be the beginning balance of February. This now creates a problem: If my rent increases, or if my financial situation changes such that I decide to save more per paycheck, this would retroactively change the values in January, and would no longer truthfully reflect my history:

An image of a Google Spreadsheet in February after making changes to the Master sheet

You can see that increasing my rent to $2200 and increasing my savings amount to 20% correctly changes the values in February, but now incorrectly displays my starting balance from January: As noted above, it should have been $3900, but because January also refers to the Master Sheet, values in January were retroactively changed, thus now incorrectly displaying what I did back then, and rippling into February. Also, not displayed, the January sheet indicates that I saved more than I actually did.


This is a very crude example, and not precisely how my spreadsheet operates, but this toy example should be enough to paint a picture of what I'm trying to achieve. I suppose my question is multifaceted:

  1. Is there a methodology to prevent retroactive changes in cells when the cells they reference change? (As in, the moment a cell has a certain value, lock that value in, or overwrite the formula in the cell to the value literal.)

  2. If not, is there a system in Google Sheets to prevent cells from changing their value? (Similar to the "Protect Sheets and Ranges" functionality, but instead of protecting them from edits, protecting them from changing their value.)

  3. If not, could there be an automated script that, at the end of every month, replaces every cell in that month's sheet with the values in the cell?

Please let me know if I can further clarify anything. Thank you in advance!

6
  • My main thought is to go with 3. 1 and 2 doesn't seem possible. Are you any amount comfortable with Google Apps Script or at the very least Javascript? If not, we can help you make 3. BUT I will say, it should be possible to do it manually. Select everything (Ctrl + A) then paste values only (Ctrl + Shift + V).
    – David Tan
    Dec 28, 2023 at 12:53
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Dec 29, 2023 at 3:56
  • You are making this unnecessarily complicated. Your data should be valid at all times and be sortable. If a rent increase in February means that your rent in January is now incorrect then you are not calculating January's rent correctly. Perhaps a seperate sheet with rents that is updated when your rent changes and each month's rent calculation is a lookup to that sheet to get the correct rent. The same can be done for savings.
    – Blindspots
    Dec 29, 2023 at 4:15
  • @DavidTan - If Google Apps Script is the only way to go here, I know enough in other languages to perhaps learn. I've never used Apps Script personally, but no better time to learn than the present. Ctrl + A, Ctrl + C, Ctrl + Shift + V is very useful though, and for now that can be a solution.
    – Sander
    Dec 29, 2023 at 9:00
  • @Blindspots - Like I mentioned in the OP, the example is very crude and not precisely what I currently have. Cells in my sheet use variables from other sheets that are volatile with time to calculate their values, and I'm simply looking for a methodology to lock in the values of cells to prevent them from changing in the future. Although I admit, perhaps I am making this needlessly complicated, and the solution may be to import any variables I need onto each sheet, ensuring that they're always accurate.
    – Sander
    Dec 29, 2023 at 9:05

1 Answer 1

0

I prepared a spreadsheet to show you ways you can use regular formulas to maintain historical values while allowing your modifier values to change.

Demo Public Spreadsheet

Link to publicly shared spreadsheet

Edits you make to the spreadsheet are anonymous, and you may wish to save a copy of your own to your Google Drive to avoid issues if other visitors make changes or break something.

Sample Data and Formulas

Variables sheet

A B C D E F G H I J
1 Variable Value Effective Note Variable Names Opening Balance Column Letters
2 Savings 0.1 1/1/2023 Savings $ 7,000.00 A
3 Rent 2000 1/1/2023 Rent B
4 Savings 0.2 2/1/2023 C
5 Rent 2100 3/1/2023 D
6 E
7 F

Data sheet

Formula is in E1 This formula and its results are not needed anywhere else and can be removed

={"Savings"; BYROW(A2:D, LAMBDA(r, 
    IF(INDEX(r,,2)="Paycheck",
    QUERY(Variables!A:C, "select B where 
    C <= date '"&TEXT(INDEX(r,,1),"yyyy-mm-dd")&
    "' and A = 'Savings'
    Order by C Desc Limit 1",0)*INDEX(r,,4),)))}
A B C D E
1 Date Note Expense Income Savings
2 1/1/2023 Groceries $ 100.00
3 1/6/2023 Paycheck $ 1,425.00 $ 142.50
4 1/9/2023 Restaurant $ 50.00
5 1/20/2023 Paycheck $ 1,390.00 $ 139.00
6 1/25/2023 Groceries $ 45.00
7 2/3/2023 Paycheck $ 1,405.00 $ 281.00
8 2/6/2023 Movies $ 36.00
9 2/9/2023 Groceries $ 61.00
10 2/12/2023 Travel $ 78.00
11 2/15/2023 Groceries $ 56.00
12 2/17/2023 Paycheck $ 1,439.00 $ 287.80
13 2/20/2023 Groceries $ 80.00
14 3/3/2023 Paycheck $ 1,380.00 $ 276.00
15 3/7/2023 Restaurant $ 92.00

Summary sheet

Formula is in A1

=LET(rDat,Data!A2:E, rVar,Variables!A:C, opnBal,Variables!H2,
   aDat,QUERY(rDat, "where Col1 is not Null",0),
   note,INDEX(aDat,,2), aVar,QUERY(SORT(rVar,3,0),
      "where Col3 is not Null",0),
   qry,LAMBDA(m, tx, QUERY(aVar, "select Col2 where Col3 
     <= date '"& TEXT(m,"yyyy-mm-dd")& "' and
     Col1 = '"& tx& "' limit 1",0)),
   moF,LAMBDA(mo,do, ARRAYFORMULA(EOMONTH(INDEX(aDat,,1),mo)+do)),
   sPrb,LAMBDA(m,c, SUMPRODUCT(INDEX(aDat,,c),  mFn(,)=EDATE(m,1)-1)),
   spF,LAMBDA(m,c,t, SUMPRODUCT(INDEX(aDat,,c), (moF(,)=EDATE(m,1)-1),
     IFERROR(1/(1/(t<>""))*(note=t),1))),
   arr,BYROW(UNIQUE(moF(-1,1)), LAMBDA(r, {r, spF(r,3,) + qry(r,"Rent"),
     spF(r,4,), (spF(r,4, "Paycheck")*qry(r,"Savings"))})),
   mon,INDEX(arr,,1), exp,INDEX(arr,,2), inc,INDEX(arr,,3),
   ocF,LAMBDA(do, BYROW(arr, LAMBDA(r, 
     SUM(opnBal, ARRAYFORMULA((inc)*(mon<r+do)-(exp)*(mon<r+do)))))),
   {"Month", "Opening"& CHAR(10)& "Balance", "Expenses"& CHAR(10)& "(incl. Rent)",
    "Income", "Closing"& CHAR(10)& "Balance", "Savings";
   SORT({mon, ocF(0), exp, inc, ocF(1), INDEX(arr,,4)},CODE(H2)-64,0+I2)})
A B C D E F G H I J
1 Month Opening
Balance
Expenses
(incl. Rent)
Income Closing
Balance
Savings SORT COL Month
2 3/1/2023 $ 8,153.00 $ 2,192.00 $ 1,380.00 $ 7,341.00 $ 276.00 A 🡇 DESC
3 2/1/2023 $ 7,620.00 $ 2,311.00 $ 2,844.00 $ 8,153.00 $ 568.80
4 1/1/2023 $ 7,000.00 $ 2,195.00 $ 2,815.00 $ 7,620.00 $ 281.50

Data Organization

There are only three sheets in the demo spreadsheet.

  • The intent was to combine the daily transaction entries, separate the modifiers, and also separate the presentation layer (eg. reporting).
  1. Data contains the individual transactions for all months.
    • There is a single formula in Data!E1 to calculate the suggested savings for each Paycheck.
      • it is placed in the heading row so that it's not affected if the transactions are sorted, or rows are inserted and deleted.
      • Neither the formula nor the data it returns, are used anywhere else. For example, the Summary sheet reports on Savings but performs its own calculation.
    • You could split up the monthly transactions with a sheet for each month however avoiding that simplifies maintenance.
      • The sort order of the date-based transactions doesn't matter.
      • new sheets don't need to be added every month
      • When formulas do need to be modified, there are far fewer that are affected, and in fewer places.
  2. Variables stores the modifier’s. Currently, there are only rent and savings however others can be added in the same way.
    • The modifier variable names in Column F populate the variable name dropdowns in Column A
    • Columns A to D hold the old and new modifier values and each has an 'effective date'.
      • This date is what freezes the values for older transactions.
    • Cell H2 holds the opening balance that precedes the very first transaction.
  3. Summary is an example of a reporting interface or presentation layer. It reports the totals by month for all months in the same place.
    • Another sheet of this nature could be added to report individual transaction line items limited to a specific month, or a date range using dropdown filters or defaulting to the most recent period(s). This could eliminate the need for duplicate monthly sheets.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.