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

Jen_M

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

Kelly Moore

@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")))))))))))

Kelly Moore

Hey @Jen M.

The {Eligibility Review Status} range - is that referring to only a single cell in your other sheet, or is an entire column? Also, do the column names in your formula show up in color? Is the last parenthesis blue?

Also, to look closely in your formula for sources of unparseable errors, I deleted the unnecessary parentheses in your formula. I think, although they were extra, you had them properly closed off. Since stray parentheses always cause an unparseable error, consider using only the minimum required.

=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”)))))))))))

Please let me know about the cross sheet reference

Kelly

Jen_M

Hi Kelly, I realized I accidentally have two threads of this conversation going. New to Smartsheet Community and was working late night when I posted. The reference for the {Eligibility Review Status} range is referring to the entire column. And the last parenthesis is not blue. Do you think I need to add in an Index/Match Formula from the reference sheet, the each row has its own ID #. Hope that makes sense. I tried your edit above but it yielded UNPARSEABLE.

TIA!

Kelly Moore

Hey @Jen_M

I think you have a few issues with your formula. If your last parenthesis is not blue, you don't have the correct number of parentheses. Blue doesn't mean the parentheses are in the right place, but the count is correct. Add, delete to get to blue at the end.

You cannot use an entire column and ask the IF whether it equals 'Ineligible'. Potential functions that work in cross sheet references include: COUNTIFS, Index Match or Index Collect to find a way to filter the data for that 'ineligible' response you need. For now, I suggest deleting that first term out of your nested IF and get the rest of the IFs working without error. Separately, I suggest working the cross sheet referenced formula - I keep a test sheet just for this purpose. Once it is working you can add it back into the IF. I'm happy to help with the cross sheet formula, if you need help. Let me know

here's your cleaned up formula without the cross sheet term. Try pasting this into your sheet. It was hard for me to keep count on paper, so add, delete parentheses at the end to get to blue if I miscounted.

=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”))))))))))

Will this first step work for you? If it doesn't work, please share a screenshot of the colored formula. And if you @mention me, the email is highlighted in my inbox.
Kelly

Jen_M

@Kelly Moore Here is a screenshot of the formula, unfortunately it is still returning UNPARSEABLE.

Thanks for your continued help.

Kelly Moore

@Jen_M

Check your columns. None of the fields in the formula are colored. I scanned but didn't see any missing brackets around the column names. I would begin deleting each field from your formula and reinserting by clicking into the actual column (sometimes because the formula is in the way you have to click a different row in the column then manually change the row# to @cell). You can stop reinserting when your field names show colors.

Kelly

Kelly Moore

Also, I can't tell in the screenshot but make sure the quotes around the responses are the straight quotes and not curly/smart quotes.

Jen_M

@Kelly Moore I double checked the formula per your instructions above and now the return is INVALID OPERATION.

I think we are getting closer? lol

Kelly Moore

@Jen_M

Try this

=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"))))))))))

Jen_M

@Kelly Moore this works! thank you. The only part missing is first part for Ineligible.

Kelly Moore

@Jen_M

We now need to work to figure out what field or combination of fields on your target sheet can help filter down to the right row on your 2nd sheet. What fields are in common?
Kelly

Jen_M

'Application ID' and 'Final Eligibility Status" are found on both sheets. The main sheet where the above formula is located, uses a reference formula to pull in the final eligibility status from the secondary sheet.

Kelly Moore

If both the {Eligibility Review Status} and the current sheet have an unique identifier [Application ID] then try this. Remember you will have to manually create the reference for the whatever your Application ID is called on the 2nd sheet.

=IF(COUNTIFS({Eligibility Review Status},"Ineligible", {Application ID}, [Application ID]@row)>0, "Ineligible")

Does this work?

Kelly

Jen_M

Yes, I created another column and added that formula separately, it returns the correct value. I guess the next step would be to nest that in the larger formula?

Kelly Moore

@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")))))))))))

Jen_M

@Kelly Moore just wanted to circle back and say thanks for all your help! this worked out well and my team is very happy :)