Table Matrix

Kaleb

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.

Paul Newcome

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.

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

@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

Heatmap.pdf

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

@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

@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

@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

@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

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

@Paul Newcome Found a Quick Workaround. See Below