How do I make a lookup column value static whenever a new record as been added?

[rybnz]

Hi all,

I don't even know if this is possible.

Say, I have the following setup in a table

Column A = Date
Column B = Expense Amount
Column C = Current Available Account balance (arrayformular vlookup from another sheet)
Column D = Recorded Account balance at the time of expense

What I want to do is whenever a new record is added, Column D would keep the value displayed from Column C at the time, and doesn't change anymore despite column C account balance will increase or decrease overtime.

For example:

First transaction when recorded

Date	Amount	Available Balance (formular)	Recorded Bal at the time (static)
11/05	-$20	$100	$100

We can see, initially I had $100 available, I'm recording an expense of -$20
My Column D recorded Balance at the time of this transaction will need forever to stay at $100, as a historical record.

Then, when a 2nd expense is recorded

Date	Amount	Available Balance (formular)	Recorded Bal at the time (static)
11/05	-$20	$80	$100
13/05	-$40	$80	$80

Column D for 11th May is still $100, where as column C the current account balance will now show $80, as $20 was expensed during the first transaction.

I hope this is making sense, thanks to anyone who can share some lights.

I was thinking this would be an automated action but are quite sure what action would be required as it's been 3 months since I last did any development, and my short term memory is really bad, like i have a 2kb storage.......

[dbaum]

If you need a calculated value to populate a column at the time of row creation and not change subsequently, put the expression that calculates the value in the column's initial value property.

[rybnz]

Thank you for replying so promptly, do I do that on my googlesheet or in the app.

If it's in the app, but i already have column with formulars on googlesheet, do i create a virtual column in the app instead?

[dbaum]

---

@rybnz wrote:

do I do that on my googlesheet or in the app

---

The initial value property is only available in the app's column configuration--there's no such thing in a spreadsheet.

---

@rybnz wrote:

do i create a virtual column

---

The initial value property is not applicable to virtual columns. Create a column in your data source.