Report: Locations that have not submitted a survey
I have a Smartsheet form for locations to submit answers to a survey. Is there a functionality to build a report/listing or some way to showcase which locations have NOT submitted a survey? I assume I would have to reference a complete list of locations to determine if the location is listed in the master data sheet (survey answers sheet). I just don't know what formula or reference to use. Thanks!
Answers
-
You would definitely need a list of every location.
Then in a flag type column you can use
=IF(COUNTIFS({Form Sheet Location name Column}, [Location Name Column]@row) = 0, 1)
This will flag any row that does not have a form submitted. You can then either filter this reference sheet or build a report based on this column being flagged.
-
Thanks Paul. So I should create a separate sheet with the entire list of locations and a flag column with the formula you stated?
-
@Paul Newcome I did get that to work, so thank you. I would like to take it a step further to add two flag columns: One to flag if the location is not listed and is categorized as a certain section (ie "Medication Prep") and a 2nd to flag if the location is not listed and is categorized as "Laboratory". I hope that makes sense. :)
-
I would just add a third column to the master list and make it a dropdown to select which category it is. Then you can include that in your report filter.
-
Hi Paul - by master list do you mean the list of locations to determine if there is a submission from that location in the survey? The 'Section" is actually a drop-down part of the form that the location selects as part of the survey, so it is included in the master data received from the survey.
-
I should also mention that each location submits a survey for each section type. There are roughly 6 sections. Ultimately I need to determine what locations have/have not submitted a survey for each section
-
I meant the sheet that contains the entire list of locations.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!