Table Matrix

Kaleb
Kaleb ✭✭✭✭
edited 05/03/22 in Formulas and Functions

Smartsheet Community:

Has Anyone Ever Created a Table like the one Below, in Smartsheet? I do not see it as one of the Chart Options in Dashboards.


Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is not a chart option. You would need to use image widgets for the left and bottom then metrics widgets for the data points.

  • Kaleb
    Kaleb ✭✭✭✭

    @Paul I was able to Create the table using using Individual Widgets and Titles. However, I have another Problem. I would like to Conditional Format the Red & the Green Background Areas Only and All the Font for the White Boxes Blue or Red (Manually).

    The Problem is I can only add Conditional Formatting to Columns or am I missing something?

    See PDF Attached


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. You would set up the conditional formatting in the sheet that houses these metrics. You would use a column with a formula to essentially rank each row and then use conditional formatting applied to the metrics column based on the value in the ranking column.

  • Kaleb
    Kaleb ✭✭✭✭
    edited 05/11/22

    @Paul Newcome Any Example Sheets I can Reference with that Type of Setup and Formula? I am Not Sure I Fully Comprehend what Type of Formula I would use. Can you get me Started with the Formula? My Assumption is that it would be a Greater Than Less Than Formula for Each Cell

    IF([C1]@row > 0 < 2, "1")

    For Example: Red for XL (LOE)-XS(Impact)

    My Sheet with the Data:


  • Kaleb
    Kaleb ✭✭✭✭

    @Paul Newcome

    I have the Basic Formula: =IF(AND([C1]@row >= 0, [C1]@row <= 2), "1")

    But Can I combine this Somehow with the Other Cells into 1 Formula? Example:

    IF(AND([C1]@row, [C2]@row, [C2]2, [C3]@row >= 0, [C1]@row, [C1]2, [C1]3, [C2]@row, [C2]2, [C3]@row, <=2), "1"))

  • Kaleb
    Kaleb ✭✭✭✭

    @Paul Newcome I Created the Formula the Long Way for Each Cell I want to be Red when I use Conditional Formatting for Rank. I received the Correct Result in the Rank Column, which is 1. See Below:

    =IF(AND([C1]1 >= 0, [C1]1 <= 2), "1", IF(AND([C1]2 >= 0, [C1]2 <= 2), "1", IF(AND([C1]3 >= 0, [C1]3 <= 2), "1", IF(AND([C2]1 >= 0, [C2]2 <= 2), "1", IF(AND([C3]1 >= 0, [C3]1 <= 2), "1")))


    However, when I had to the Formula to Rank Cells for a Different Color using Conditional Formatting I Still Get 1. See Below The Added Piece in Bold:

    =IF(AND([C1]1 >= 0, [C1]1 <= 2), "1", IF(AND([C1]2 >= 0, [C1]2 <= 2), "1", IF(AND([C1]3 >= 0, [C1]3 <= 2), "1", IF(AND([C2]1 >= 0, [C2]2 <= 2), "1", IF(AND([C3]1 >= 0, [C3]1 <= 2), "1"), IF([C1]4 >= 0, 5)))))

  • Kaleb
    Kaleb ✭✭✭✭
    edited 05/11/22

    @Paul Newcome I am using the Formula Wrong because it is returning the right value when it looks at those cells. Therefore, back to the drawing board

    Can you Let me know what Type of Formula I should be using?

    Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I didn't realize you also had your source data set up as a matrix. You should be able to use separate Conditional formatting rules for each color and then duplicate them for each column.

  • Kaleb
    Kaleb ✭✭✭✭
    edited 05/12/22

    @Paul Newcome Found a Quick Workaround. See Below



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!