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