Best Of

Hi @Walter Mootz, you can absolutely do this with Smartsheet and I think it's a good use case for it.

I setup an example to follow. I created a sheet named "Audit Sheet", which has audit scores. Another sheet named "Supplier Sheet", which has supplier scores. Then a third sheet named "Final Sheet", which merges the data from these two sheets.

The "Final Sheet" pulls the data from the other two sheets by using cross sheet references.

If you want to silo the information even better, you can also require your people to enter the audit scores using a Smartsheet Form, so that they cannot see the underlying data sheets.

To pull the data into the final sheet, I used this formula for "Score 1"

=INDEX({Quality Audit Score 1}, MATCH(Trait@row, {Quality Audit Traits}, 0))

Explanation of this formula:

• MATCH - Look at the rows in the "Quality Audit Traits" reference, and find the row that has the same Trait as the current row (Trait 1, Trait 2, etc).
• INDEX - Use the row that the MATCH found, and then give me the value in the Score 1 column from that sheet.

Then you can throw this final sheet into PowerBI.

I hope this helps!