How can I summarize 26 sheets and then generate a document
This is a complex one that has been rattling around my brain for a while now. We have 26 sheets, each has a form tied to it that allows folks to enter information based on review/compliance questions. Example: AB 1.1.1 Did you observe them following rules? - If yes they receive a % score if No they receive a 0 and in the following logic/column they describe what was observed.
I need to pull just the 'No's of all 26 sheets and the descriptions that match those.
I thought about a helper sheet - but since these are Forms into a sheet - all the data is unique columns. So once copied to the helper sheet you end up with many rows and many blanks.
The end result we want is a PDF generated that summarizes all 26 areas/sheets and all the 'No' responses along with 'observations'. The idea is you'd receive an entry for each sheet for 1 site. Example: Site A responds to each form/sheet for the Audit for February - you can then look to summarize all responses for site A marked February and summarize.
I've tried a few different ways but looking for that idea that will trigger the right setup!
THANKS COMMUNITY!
Answers
-
You say "all the data is in unique columns". Do you mean that you have the question "Did you observe them following rules?" on every form but in some forms the answer goes into a sheet with column named X and sometimes into a column named Y?
If the column names were the same across all the sheets, a report would probably do exactly what you need - allow you to pull the columns from each of the sheets into one report, with a filter to include only rows where the answer is "no".
You can export reports as PDFs. You can also summarize on the report.
The key thing is that the column names on each sheet must match. For example, answers to "Did you observe them following rules?" must always go into a column called "AB 1.1.1" regardless of which sheet that column is on, the name must be the same.
If you can do this, then try a report (click on the create option, choose Report, and then Row Report, select your 26 sheets and the columns that you want to include).
-
The unique columns unfortunately are different names. So sheet AB has AB 1.1.1, AB 1.2.1, AB 1.3.1 and sheet CD has CD 1.1.1, CD 1.2.1, CD 1.3.1 etc.
So the summary/reports never seem to work since the names are different.
We did it this way to be able to customize the forms and not create an extremely large (many columns) sheet with multiple forms.
I think we'll have to combine things in powerbi - so we have a solution, but just making sure I didn't miss any options in smartsheet.
-
We could probably do something in Smartsheet to get the data into the same columns so you can generate the report but if you have an alternative solution you could probably do that just as quickly. A report would be perfect and simple, if the column names were the same.
-
I think I'd have to create a helper sheet where the leading alpha (example AB, CD, etc) become a 'category'. Then we merge the other numbered columns 1.1.1, 1.2.1, etc. Then I suppose we could do a report summary based on category.
-
That would work.
