I have a spreadsheet which calculates a value in a cell. There is a date cell as well. What I want to be able to do is save the value in the 'Value Cell' to a table with a date (month) column. But, only update the row in the table if the date (month) is current.
Broadly speaking it looks like that shown below (and attached).
Date 18-Oct-21 Oct-21
Value 100 Saved Values
Oct-21 100
Nov-21
Dec-21
Jan-22
Feb-22
Mar-22
Apr-22
May-22
As long as the date is in October 21, then the Value column is stored in the Saved Values table next to Oct-21.
If on 31st October 21 the value is 105, then the table would show 105 against Oct-21. If on 1st November 21 the value increases to 106, the Oct-21 would still show 105 but now Nov-21 would have 106 against it.
For the Saved Value cell I have written this;
=LET(
CurrentValue, $F5,
IF(E5=$F$1,$C$4,CurrentValue))
But this leads, understandably, to a circular reference. I could disable circular reference checking for the whole worksheet, but this feels wrong. Can anybody suggest another way? VBA?
Bookmarks