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.......