Trinary outputs for IF function -AND- display metric if Reporting Month<Current Month

Tommy Dean

Hey @Paul Newcome - I bet you are the PERFECT person to help me with this as I've used many of your solutions from countless posts and you may even recognize some of your work below!

🏳️ HALP ME! 😲

In SmartsheetGOV, I track and report on staff members' Monthly Reports (July 2020 - May 2021). I have 3 Grids, 1 Form, 8 Reports, and 1 Chart for multiple dashboards. Everything has worked and displayed as desired, until now. I need to evolve an IF formula and add another metric on the Chart.

Sheet 1 (Monthly Reports)

Collects raw data from staff members via a form and new entries are located at the bottom of the sheet . It requires:

1. Fellow Name (Last Name, First Name) (Contact List)
2. Report Month/Year (form) (Dropdown-Single Select)
3. Via Form? (Dropdown-Single Select) which is a Hidden Field and "Yes" is the Default Value

Additionally, the sheet has two Date columns: First Day and Due Date. After all monthly reports have been submitted for last month, I configure the first "Formula" row (at the bottom) to be a future Parent because formulas won't inherit if the row(s) above are Children... 🤬. I manually enter the 1st of the Month and it fills down with: =[First Day]1. In the other, I manually enter the 5th of the Month and fill down with: =IF([Via Form?]@row = "Yes", [Due Date]1)

Sheet 2 (Monthly Report Summary)

Feeds from Monthly Reports and provides data for most of the 8 Reports.

It has columns:

1. Exited (Checkbox)
2. Fellow Name (Last Name, First Name) (Contact List)
3. Due Date (Date) - manually entered in Cell 1 and fill down with: =[Due Date]$1
4. July Submitted through May Submitted (Text/Number)

The formula for every person/month is:

=IFERROR(IF(INDEX(COLLECT({Monthly Reports - Via Form}, {Monthly Reports - Fellow Name (Last/First)}, $[Fellow Name (Last Name, First Name)]2, {Monthly Reports - Report Month}, [July Submitted]$1), 1) = "Yes", "Yes", "No"), "No")

Simply stated, if a staff member's report is found in Monthly Reports for the respective month, it is "Yes", otherwise it is "No". It has been a perfect output for these binary results, with multiple dynamic conditions, but it needs to get even more complex...

Sheet 3 (Metrics/KPIs)

Also feeds from Monthly Reports to populate the Chart.

DESIRED RESULTS

Here is the criteria to build upon the IFERROR(IF(INDEX(COLLECT formula:

1. Monthly Report is required when they have not Exited
   1. If Submitted, output = Yes
   2. If Missing, output = No
2. Monthly Report is required when they Exited last month
   1. If Submitted, output = Yes
   2. If Missing, output = No
3. Monthly Report is not required when they Exited before last month
   1. If Missing, output = N/A

It would be best to have the quantity of Required-Missing reports in Metrics/KPIs to easily add it to the chart (i.e. count the "No"s).

MY ROADBLOCK

No matter what I've tried, I can't get a trinary output (Yes, No, or N/A). The closet I've got are 2 formulas for Yes-No or Yes-N/A.

I have a ton of other helper columns for different things but I don't imagine they are relevant to this dilemma.

I hope to hear from you soon! This is one of those that keeps me up 'til 3am...

~Tommy

Paul Newcome

Try something like this...

=IF([Due Date]@row <= TODAY(), IF(Exited@row <> 1, IF([Report Found?]@row = "Yes", "Submitted", "Missing"), "N/A"), "N/A")

Paul Newcome

So we will start with this piece...

Lets give this a try...

=IF(Exited@row = 0, IF([Report Status]@row = "Yes", "Yes", "No"), IF([Report Status]@row = "Yes", "Yes", "N/A"))

Basically we have two IF statements looking at the [Report Status] based on whether or not Exited is checked.

=IF(Exited@row = 0, run this [Report Status] IF, otherwise run this other [Report Status] IF)

Tommy Dean

Thank you for a quick response @Paul Newcome!

Your formula is a beautiful helper! Is it possible to incorporate that in:

=IFERROR(IF(INDEX(COLLECT({Monthly Reports - Via Form}, {Monthly Reports - Fellow Name (Last/First)}, $[Fellow Name (Last Name, First Name)]2, {Monthly Reports - Report Month}, [July Submitted]$1), 1) = "Yes", "Yes", "No"), "No")

So it will still look for a Via Form? "Yes" entry (which I'm going to change to "Submitted") in the other sheet based on Name & Report Month and if Exited=0 it's "Yes", "No" or Exited=1 it would be "Yes", "N/A"?

~Tommy

Tommy Dean

Hey @Paul Newcome - so scratch my response... I realized I left out a critical component in the criteria within my original post and the picture of my roadblock picture was misleading showing "Report Status" with manually entered "Yes" or "Missing" (for example purposes). It should have been real data (i.e. [Month]Submitted using IFERROR(IF(INDEX(COLLECT to generate data) 😫

But you did help me out and I'm getting closer....

Monthly Reports are required if the Month's Due Date is less than or equal to Today AND they have not exited. If it is required, then the report should be queried. Here's a layout of what I'm trying to get one formula to do.

Checks if Monthly Report is required

=IF([JUL Required]$1 <= TODAY(), IF($Exited@row = 0, "Yes", "No"), "No")

Looks for their Monthly Report

=IFERROR(IF(INDEX(COLLECT({Monthly Reports - Row Purpose}, {Monthly Reports - Fellow Name (Last/First)}, $[Fellow Name (Last Name, First Name)]2, {Monthly Reports - Due Date}, [JUL Required]$1), 1) = "Submitted Report", "Found"), "Not Found")

~Tommy

Paul Newcome

Try something like this...

=IF([Due Date]@row <= TODAY(), IF(Exited@row <> 1, IF([Report Found?]@row = "Yes", "Submitted", "Missing"), "N/A"), "N/A")