How to display a list of cell values in a report based on certain criteria
Hello,
I'm pretty new to Smartsheet.. I have been researching, but not seeing an answer so far. Here is the situation:
- We have a process to inspect a vehicle, wash the vehicle, and load the vehicle (one of 1500 vehicles in a fleet).
- It's a two-part process - inspect and wash (then park), and then inspect and load.
- A vehicle is checked on multiple items, and if it does not pass inspection, it can be rejected (for correction or repair)
- Each vehicle has a unique ID
I have two sheets / forms:
- Sheet 1: Wash and Inspect. In the associated Form for sheet 1, the person enters the unique ID as part of filling the form. If any part of the inspection fails, a free text field appears where they can explain what is wrong. If the vehicle passes inspection, Data Shuttle is used to populate a dropdown list in Sheet 2 of available vehicles to inspect and load. A formula in Sheet one looks a sheet two to remove the vehicles from the dropdown that have been loaded (passed second inspection).
- Sheet 2: Inspect and Load. The associated form provides the user the ability to choose from a dropdown (to reduce human error) to select a washed vehicle. A second inspection (like the first one) is recorded, and if the inspection points pass, then the vehicle can be loaded. Like the first form, if any inspection point fails, a free text field appears so the user can explain the reason.
I would like to have a dashboard that shows a list of vehicles that have been rejected, and the comments entered… no other information would be needed - except perhaps the date it was rejected (automatically recorded when the forms are submitted). Ideally it would be a combination of the rejected vehicles from both sheets.
Is it possible to combine the rejected vehicles, their IDs in to one easy view on a dashboard that is automatically updated (eventually the vehicle issues would be corrected or repaired)?
Thanks in advance!
Best Answer
-
Hello @Kpyto71 ,
I'd be happy to help!
- Create a row report.
- Select both sheets (Sheet 1 and Sheet 2) as your "Source Sheets"
- Select only necessary columns to show (For example: Name of the Vehicle, Unique ID, and Comment)
- Optional. Group by Sheet Name if needed or you can just add the Sheet column so you'll know which sheet the row is from.
- Save.
- On your dashboard, add a Report widget.
- Select the newly created row report.
Note that it is highly recommended to have consistent column names on both sheet 1 and sheet 2 to have a seamless view of the report.
You can check these articles for references:
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined
Answers
-
Hello @Kpyto71 ,
I'd be happy to help!
- Create a row report.
- Select both sheets (Sheet 1 and Sheet 2) as your "Source Sheets"
- Select only necessary columns to show (For example: Name of the Vehicle, Unique ID, and Comment)
- Optional. Group by Sheet Name if needed or you can just add the Sheet column so you'll know which sheet the row is from.
- Save.
- On your dashboard, add a Report widget.
- Select the newly created row report.
Note that it is highly recommended to have consistent column names on both sheet 1 and sheet 2 to have a seamless view of the report.
You can check these articles for references:
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
Thank you Melissa! Exactly what I was looking for, and thanks for the tips of keeping column names the same!
C.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives