Need help with monthly form tracking formula to Master sheet

Dina K.

Intake Form sheet for each site that has submitted their monthly activity form. Entries will be in this sheet for the entire current year, so each site ‘might’ have up to 12 monthly entries.

Each of the 60 sites can have multiple entries, the 2 sheets are linked with Site ID (form sheet) Site # Text (master sheet)

If the [Month] column is equal to “6” I need a formula in the Master sheet to show “Complete” otherwise show as “Missing” for that month (Jun 2024)

Master Sheet, tracking each of the 60 Sites when/if they completed the monthly activity form

I’ve tried so many combinations of INDEX/COLLECT, INDEX/MATCH and nothing seems to work. Been searching the Community for days. Any help would be greatly appreciated! ❤️

jmyzk_cloudsmart_jp

Hi @Dina K.

You can use the COUNTIFS formula like this;

=IF(COUNTIFS({Activity Entry Sheet : Site # Text}, "" + @cell = [Site # Text]@row, {Activity Entry Sheet : Year}, 2024, {Activity Entry Sheet : Month}, 7) > 0, "Complete", "Missing")

https://help.smartsheet.com/function/countifs

The"" + @cell = [Site # Text]@row condition is somewhat difficult. Since your site # Text has values like 00137 and 00144, just putting the {range}, criteria combination like the following is comparing 000137 to 137 and 00144 to 144. So, I added "" in front of [Site # Text]@row.

{Activity Entry Sheet : Site # Text}, [Site # Text]@row

When you get the COUNTIFS values that meet the Site #, Year, and Month criteria, you can determine which to show as "Complete" or "Missing" using the IF statement.

You could show a "Multiple Entries" warning IF the COUNTIF value is > 1. (To do that, you want a helper column like 2024 July Count, and make the formula easy like;

=IF([2024 July Count]@row>1,"Multiple Entries", IF([2024 July Count]="Complete","Missing"))

https://app.smartsheet.com/b/publish?EQBCT=618ad90711254fcb9e591703c4e39b7e

https://app.smartsheet.com/b/publish?EQBCT=933ac76d94634fa0b7b4e50f983e172a

Dina K.

@ https://community.smartsheet.com/profile/jmyzk_cloudsmart_jp you are a GENIUS!!!!!!! Thank you so much! worked perfectly! You have been a great help to my co-worker as well. Thank you so much for helping me and other folks out on this Community Board ❤️

Marlana K.

@jmyzk_cloudsmart_jp You should wear a cape! Thanks for all you do in the community. (I'm the co-worker) and you have helped me in the past and this will help with another solution I am creating.

jmyzk_cloudsmart_jp

Kind words like yours keep me going! Happy to help anytime.😁