How to stop a calculated column executing the formula once it is populated

Question

I have a SharePoint list. The calculated column in discussion is names as [Query Date] and it should populate the date value when another column [Status] value changes to "Queried". I can make this happen with simply formula but the challenge is when the Status column value changes from Queried to Query Completed or any other choice value the [Query Date] value go to blank!!! I need this column value to be static after the first modification based on the formula.

I used something like this (stupid idea it seems) but gave me the circle reference error.

=IF(ISBLANK([Query Date]),IF([Status]="Queried",Today(),0),0)

Basically I wanted that column to stop executing the formula if it is previously updated.

Answer 1

Hi @Anonymous ,

Calculated column will be changed dynamically based on other columns, unless you specify a certain value to make it static.

Make a simple example, in below formula, if the [Status] column is equal to "Queried", the calculated column will display "Queried", and under other conditions it will show static value: "Queried".

=IF([Status]="Queried",[Status],"Queried")

Therefore, after the first modification based on another column, it is impossible to make the it static，the workaround is to set the static value directly.

---

If an Answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.