Data Entry Suggestions for Siloing Data for Radar Chart

Hey everyone,

I’m exploring the best way to use Smartsheet for collecting score card data and formatting it for a Radar Chart. Ideally, I’d like to:

Enter audit results in Smartsheet
Modify or structure the data in another sheet
Format the combined data for visualization

The goal is to merge audit percentages from the quality team with supplier scores from purchasing to create a unified grading dataset. Would Smartsheet be the right tool for this, or would it be better to pivot the data within Power BI?

This is the formatting that is required with each Supplier requiring it's own column and the traits that are being graded as selections.

These then go into PowerBI to generate my report.

Looking forward to your suggestions!

Find more posts tagged with

Forms

Integrations

Solution Center

Sheet Summary

Power BI

Accepted answers

SSFeatures

@Walter Mootz

You could have these dated. You would want to have a Date column in your Audit sheet and a Date column in your Supplier sheet.

Then the formula that I mentioned above would need to be changed. Previously, we only needed to match the rows that had the same trait as the Trait sheet. But now we would need to match the rows that had the same trait as the Trait sheet, AND, the dates would need to be within the specified year.

We could use COLLECT and JOIN and it would look something like this:

=JOIN(
  COLLECT(
    {Quality Audit Score 1}, 
    {Quality Audit Traits},
    Trait@row 
    {Quality Audit Date}
    YEAR(@cell) = 2025
  )
)

This formula is going to return all of the Audit scores that match the current Trait in the row, AND, check that the Date in that row has the year 2025.

You mentioned that all of the traits will be different. To be able to merge the data between the two sheets, we need some type of unique identifier to join them.

Is there a product ID or product name or something like that? What is it that the Quality team and Purchasing team are auditing?

For example, if the Quality Audit Sheet had something like:

Product 1, Trait 1, Trait 2, Trait 3

And the Purchasing Audit Sheet had something like:

Product 1, Fill Rate, Timeliness, Communication

Then we could merge everything by using the "Product 1" as the key.

Best!

SSFeatures

@Walter Mootz You're very welcome!

Ok thanks for clarifying about the traits. I'll keep going with the example where you want to be able to separate out the information based on each year.

You can have a column for each supplier and each year. For example:

ACME 2025
ACME 2026
Winter 2025
Winter 2026

The formula in each column will be the same, except for the year part of the formula. Additionally, each column will need 2 different formulas. One formula will be for traits 1-8, and it will reference the Quality sheet. The second formula will be for traits 9-16 and it will reference the Purchasing sheet.

Here is an example using ACME 2025, and these formulas use Cross Sheet References:

Formula 1 (rows 1-8) (References Quality Sheet):

=JOIN(
  COLLECT(
    {Quality Sheet Scores}, 
    {Quality Sheet Traits},
    Trait@row,
    {Quality Sheet Date},
    YEAR(@cell) = 2025
  )
)

Formula 2 (rows 9-16) (References Purchasing Sheet):

=JOIN(
  COLLECT(
    {Purchasing Sheet Scores}, 
    {Purchasing Sheet Traits},
    Trait@row,
    {Purchasing Sheet Date},
    YEAR(@cell) = 2025
  )
)

For ACME 2026, you would modify the formula to use 2026.

Best!

All comments

SSFeatures

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!

Walter Mootz

I will give this a try and see if it makes sense. I will reach out with results.

Walter Mootz

One point of note, I was looking to have these dated, so I wouldn't have to create new sheets each year. You think there might be an option for that? Or would it be better to break it down into having one of these for each year?

Also, I like the idea of the two separate audits (one from Quality, the other from purchasing) but the traits won't be the same as they'll be looking at different items. How will that work with pulling in the information in the final sheet?

For example:

Quality Audits Supplier 1 - They have 3 traits audited (for this example, but there's going to be about 8) , we'll say Quality Program Presence, Defective Findings, Production Rejects.

Purchasing audits Supplier 1 - They have 3 different traits, We'll say Fill Rate, Timeliness and Communication.

How do i get those to pull into that final sheet as they're all different traits?

SSFeatures

@Walter Mootz

You could have these dated. You would want to have a Date column in your Audit sheet and a Date column in your Supplier sheet.

We could use COLLECT and JOIN and it would look something like this:

=JOIN(
  COLLECT(
    {Quality Audit Score 1}, 
    {Quality Audit Traits},
    Trait@row 
    {Quality Audit Date}
    YEAR(@cell) = 2025
  )
)

This formula is going to return all of the Audit scores that match the current Trait in the row, AND, check that the Date in that row has the year 2025.

You mentioned that all of the traits will be different. To be able to merge the data between the two sheets, we need some type of unique identifier to join them.

Is there a product ID or product name or something like that? What is it that the Quality team and Purchasing team are auditing?

For example, if the Quality Audit Sheet had something like:

Product 1, Trait 1, Trait 2, Trait 3

And the Purchasing Audit Sheet had something like:

Product 1, Fill Rate, Timeliness, Communication

Then we could merge everything by using the "Product 1" as the key.

Best!

Walter Mootz

Thanks for all your help Nathan.

The only common trait these would have is the vendor name. Quality is looking at traits 1-8 and Purchasing is looking at a different set of traits 9-16. But it'd be for the same vendor.

SSFeatures

@Walter Mootz You're very welcome!

Ok thanks for clarifying about the traits. I'll keep going with the example where you want to be able to separate out the information based on each year.

You can have a column for each supplier and each year. For example:

ACME 2025
ACME 2026
Winter 2025
Winter 2026

Here is an example using ACME 2025, and these formulas use Cross Sheet References:

Formula 1 (rows 1-8) (References Quality Sheet):

=JOIN(
  COLLECT(
    {Quality Sheet Scores}, 
    {Quality Sheet Traits},
    Trait@row,
    {Quality Sheet Date},
    YEAR(@cell) = 2025
  )
)

Formula 2 (rows 9-16) (References Purchasing Sheet):

=JOIN(
  COLLECT(
    {Purchasing Sheet Scores}, 
    {Purchasing Sheet Traits},
    Trait@row,
    {Purchasing Sheet Date},
    YEAR(@cell) = 2025
  )
)

For ACME 2026, you would modify the formula to use 2026.

Best!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.