Hello, I am trying to have a Status Column update to a specific status based on set criteria from other dates and data entries from a main sheet, and reference sheets. There are 12 statuses in total. This is the formula I have tried, but I get UNPARSEABLE as the return. =IF({Eligibility Review Status}=”Ineligible”, “11. Ineligible”, (IF([Date of Withdrawal]@row >0, “12. Withdrawn”,(IF([Date Closeout Approved]@row >0, “10. Complete”,(IF([Date Disbursement Issued to Applicant]@row>0, “09. Pending Closeout”,(IF([Date Award Signing Event Scheduled]@row >0, “08. Pending Disbursement”,(IF([Date Approved by Agency]@row>0, “07. Approved and Pending Signing Event”,(IF([Date Approved by Grant Specialist]@row >0, “06. Request for Agency Approval”,(IF([Date Approved by Lead Inspector]@row >0, “05. Final Eligibility Review”,(IF([Date of Prelim Eligibility Review]@row >0, “04. Residential Property Assessment”,(IF((AND([Completed Application]@row=1, [Photo ID]@row =1, [Proof of Primary Residency]@row=1, [Proof of Legal Residency]@row=1, [Proof of Ownership]@row=1, [Income Information]@row =1)), “03. Initial Eligibility Review”,(IF([Date Application Received]@row >0, “02. Application Received”,“01. Application Pending”))))))))))))))))))) The first IF statement for Eligibility Review Status is a reference to another sheet, the remaining dates and entries are a comb of being from the main sheet (as in, the user enters in the information there) or the date is being pulled into the main sheet with a reference formula. Thanks, Jen

@Jen_M =IF(COUNTIFS({Eligibility Review Status},"Ineligible", {Application ID}, [Application ID]@row)>0, "Ineligible", IF(ISDATE([Date of Withdrawal]@row), "12. Withdrawn", IF(ISDATE([Date Closeout Approved]@row), "10. Complete", IF(ISDATE([Date Disbursement Issued to Applicant]@row), "09. Pending Closeout", IF(ISDATE([Date Award Signing Event Scheduled]@row), "08. Pending Disbursement", IF(ISDATE([Date Approved by Agency]@row), "07. Approved and Pending Signing Event", IF(ISDATE([Date Approved by Grant Specialist]@row), "06. Request for Agency Approval", IF(ISDATE([Date Approved by Lead Inspector]@row), "05. Final Eligibility Review", IF(ISDATE([Date of Prelim Eligibility Review]@row), "04. Residential Property Assessment", IF(AND([Completed Application]@row=1, [Photo ID]@row =1, [Proof of Primary Residency]@row=1, [Proof of Legal Residency]@row=1, [Proof of Ownership]@row=1, [Income Information]@row =1), "03. Initial Eligibility Review", IF(ISDATE([Date Application Received]@row), "02. Application Received","01. Application Pending")))))))))))

IF Statement to Update a Status Column based on data from Main Sheet and Reference Sheet

Answers

Kelly Moore ✭✭✭✭✭✭

05/24/24

Happy it's now working.
Kelly

0 · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

OFFSET calculation
Hello, I am wondering if I could have some help on a calculation matter. As I have tried to find quickly some answers on the forum, it seems that you can not use the OFFSET function from Excel in Smartsheet. Right now I was using it in column H to calculate the stock of the compounds in the inventory. The goal is to add…
Sumifs
Hello, I was wondering if anyone could help me with a sum ifs function. A screenshot of the sheet is included. Basically I want a formula that says if the course name match and the date match, total the costs. So in the example if the course date is 9/16/2024 and the course name is 300, add the three rows of cost. I can…
How to create a formula column that calculates the difference between rows?
I have a sheet1 with columns labeled "Metric Name," "Plan-Actual," and monthly columns where we record the plan and actual numbers consecutively for the same metric name. Metric Name Plan - Actual Last FY - Jun Last FY - Jul Last FY - Jaug Test-01 Plan 90 89 21% Test-01 Actual 90 79 78 Test-02 Plan 90 100 120 Test-02…

IF Statement to Update a Status Column based on data from Main Sheet and Reference Sheet

Answers

Help Article Resources

Categories