Need help with monthly form tracking formula to Master sheet
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! ❤️
Answers
-
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")
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"))
-
@
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 ❤️ -
@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.
MARLANA KALINOWSKI
Sr. Business Analysts / Smartsheet Solutions
National Pharmacy Services | Genoa Healthcare
-
Kind words like yours keep me going! Happy to help anytime.😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!