Formula to Refresh ONLY if Status is Open

Sherry Fox

Hi all! First of all, thanks for your assistance. I have a sheet, and I want to create a formula to another (a simple VLOOKUP). However on my initial sheet where the formula resides it will be based on the Status. If the status is Open, I would like the formula to make any updates. However if the record has a Closed status, I would like the record to NOT update at all. Is this even possible?

Will I need to create an IF Statement within my VLOOKUP and have 2 PM fields (Original and NEW)? Stating that IF Status = Closed, then Original PM, If Status = Open, then New PM? That is the only way I can think of that may work. I understand that I cannot have a value and a formula occupying the same cell.

Frank B.

Yes, it is possible to create a formula in Smartsheet that uses an IF statement to determine whether to update a record or not based on its status.

Assuming you want to update a field called "PM" in another sheet based on a lookup from your initial sheet, you could use the following formula:

=IF(Status = "Open", VLOOKUP(...), "")

In this formula, replace "Status" with the cell reference of the status column in your initial sheet (e.g. A2), and replace "..." with the necessary parameters for your VLOOKUP function to lookup the value you need in the other sheet.

The IF statement will check whether the status is "Open". If it is, the VLOOKUP function will be executed and the value will be updated in the PM field of the other sheet. If the status is "Closed", the formula will return an empty string and no update will be made.

Note that you cannot have a value and a formula occupying the same cell, so you will need to create separate PM fields for Original and New, and use the IF statement to determine which one to update.

Sherry Fox

@Frank B.

Thanks I will try it out on Monday, then confirm I was able to get it working.

Sherry Fox

@Frank B. ,

Yes, using the IF Statement worked. But instead of having a "", if the Status was Closed, I had the formula look in another column. I added a column to my table for "previous PM". This provides me the information as none of these cells can be blank. Thanks for the info on the IF Statement.