Report: Locations that have not submitted a survey

Courtney Collier

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!

Paul Newcome

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.

Courtney Collier

Thanks Paul. So I should create a separate sheet with the entire list of locations and a flag column with the formula you stated?

Courtney Collier

@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. :)

Paul Newcome

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.

Courtney Collier

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.

Courtney Collier

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

Paul Newcome

I meant the sheet that contains the entire list of locations.