How to display a list of cell values in a report based on certain criteria

Kpyto71

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!

Melissa Yamada

Hello @Kpyto71 ,

I'd be happy to help!

1. Create a row report.
2. Select both sheets (Sheet 1 and Sheet 2) as your "Source Sheets"
3. Select only necessary columns to show (For example: Name of the Vehicle, Unique ID, and Comment)
4. 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.
5. Save.
6. On your dashboard, add a Report widget.
7. 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:

https://help.smartsheet.com/articles/2482078-build-a-row-report-with-report-builder

https://help.smartsheet.com/articles/2482269-using-report-widgets

Melissa Yamada

Hello @Kpyto71 ,

I'd be happy to help!

1. Create a row report.
2. Select both sheets (Sheet 1 and Sheet 2) as your "Source Sheets"
3. Select only necessary columns to show (For example: Name of the Vehicle, Unique ID, and Comment)
4. 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.
5. Save.
6. On your dashboard, add a Report widget.
7. 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:

https://help.smartsheet.com/articles/2482078-build-a-row-report-with-report-builder

https://help.smartsheet.com/articles/2482269-using-report-widgets

Kpyto71

Thank you Melissa! Exactly what I was looking for, and thanks for the tips of keeping column names the same!
C.