How to set a cell value based on another column. If false do nothing to original value

I have a status column that is a dropdown list. I also have another date column "Goods and Services Received" that pulls a date using a VLOOKUP from another smartsheet. What I am trying to do is change the status column to "Complete" if there is a date in the "Goods and Services Received" column of the reference sheet, but leave the original value in the status column if there is no date in the "Goods and Services Column".

Find more posts tagged with

Formulas

Comments

Adam Wilson

The column Goods and Services Certification Received which is a column in this same sheet pulls a date from another sheet if there is a matching record. It will either have a date or indicate #NO MATCH. If there is a date in that column I want the status column changed to "Complete" If the column has #NO MATCH I want the Status column to display the original value in the Status column.

I thought this might work but it always returns #CIRCULAR REFERENCE.

=IF(ISDATE([Goods and Services Certification Received]1), "Complete", Status1)

Paul Newcome

What is the original formula in the status column?

Adam Wilson

The Status column is a dropdown list with the following options: I am trying to set the value to "Completed" only if a date is found in the column "Goods and Services Certification Received"

Appro Creation

Pending Approval

Pending PO Creation

Pending PO Approval

Work in Progress

Awaiting Certification

Completed

Converting to Fixed

Ready to Bill

SJM Billed to Accounting

Project Closed

Ongoing Project

Quote only Request

Project Cancelled

Project on Hold

Paul Newcome

You would have to automate all or none. Once a different selection is made manually it will override and delete the formula.

Sign in to join the conversation!

Quick Links

Trending discussions

Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
Circular Reference with formula
i am not good with writing formulas within a sheet. I asked the AI tool in the sheet to help me write a formula and I am getting a Circular reference back. I'm trying to have it write the following formula for me. If the column "Tracking #" is not blank, change Column "Deployment Phase" to "Shipped" I have tried adding in…
How to delay the execution of a workflow whose trigger occurred much earlier?
I have a workflow that detects a row(date) that is changing its Fiscal Week Finish Date and send this row to other Sheet. Long story short: I collect delayed tasks in separate file & I tag reasons for delay for these tasks. It happens that plan are analyzed 2-3 hours and dates are being changed several times. I would like…