Question on cell formatting within a dashboard.

[gwclint]

Hello all,


I've used several tutorials on this site to create a dashboard, several actually and they all work perfectly. I've run into an issue on this latest one and am hoping that I can get some assistance.


There are 2 dropdowns facilitating selection of an agent and the other for their various metrics. These feed a simple table which is referenced to create a line chart that show how 1 metric for 1 agent trends month to month. This all functions perfectly, my issue is that the metrics range in number formats. Some are hh:mm:ss and some are decimals. When switching between the 2 formats, my table that is being displayed will update with a new value but will show the wrong formatting, and my chart data labels are incorrect format as well. I tried to use conditional formatting to change the number formats of the cells in the table referenced by the chart, but this doesn't work properly. And the chart seems to only show decimal values.


The desired goal is that when I use the dropdown to select a metric that displays a time format that the values in my simple table will show times properly and the line chart will show the same thing, Conversely both will display decimals when I select a metric using decimal formatting.


I hope the description makes sense, and Thank you in advance!


Clint

 

[Hui]

Clint


Firstly, Welcome to the Chandoo.org Forums


As times are always between 0 and 1 you can use a custom format as

[<1]hh:mm:ss;0.0

or

[<1]hh:mm:ss;###0

etc


The first part before the ; [<1]hh:mm:ss

does the time

The section after the ; does the Number > 1


You can add a format for 0 if you want as


[<1]hh:mm:ss;0.0;0.0


etc