Dynamic Chart range (thermometer)

[pauldaddyadams]

Hi

On the sheet “Dashboard” I have a thermometer chart which I would like adapting if possible….

Is there any way I can have the axis title so it only displays the action number and not the action name also?

I would also like to extend the range but make it dynamic – I could potentially have 150 rows but I only want to populate the actual number of rows filled.

Can anyone help please?

[tim201110]

for dynamic ranges
http://chandoo.org/wp/2009/10/15/dyn...t-data-series/

[Greg M]

Hi there,

See if the attached version of your workbook does what you need.

It contains a Defined Name constant called "iMAX_ROWS" which has been assigned a value of 121 (the number of data rows in your table). This value may be changed to a more appropriate value if required.

Three dynamic named ranges have been created - one for the chart legend, one for the target percentage, and one for the percentage completed. The chart has been modified so that it takes its values from these named ranges.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[pauldaddyadams]

Hi Greg

Thanks for taking a look. I am struggling to replicate what you have done in my full model. I believe I have copied over all named ranges ok but the graph isn't updating - what am I doing wrong? I have uploaded my full working model so I don't have to replicate.

I have noticed that (with the current data set) the named range "TBLLegend" misses out row 58, how is this corrected?

Can you see why the chart doesn't update? I notice I don't have a chart data range - what should this be if I would like it Dynamic in range?

Finally the chart doesn't display the legend - it shows 1, 2, 3 rather than 1.4,1.5 etc

Sorry for all the questions!! I believe its very close to what I need

[pauldaddyadams]

I spoke to soon - it appears to be working with the exception of the legend problem (mentioned above that doesn't pick up the full range). Any idea how to correct this formula:

Please Login or Register  to view this content.

[Greg M]

Hi again,

Many thanks for your prompt feedback.

The Count function in the formula:

Please Login or Register  to view this content.

returns the number of cells which contain numeric values, and was returning a value of 28 instead of the value of 29 returned by the equivalent formulas for the target percentage and percentage completed columns. This led me to look at the data values themselves.

Cell B30 on the "BR Action Plan" worksheet contains a TEXT value of 2.11 (i.e 2.11 preceded by a ' character) - when this value is referenced in Column B of the "Dashboard" worksheet, it is not counted as a numeric value, so the above formula returns a value of 28 instead of the required 29.

Removing the ' character from the value in Cell B30 on the "BR Action Plan" worksheet resolves the problem.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

[pauldaddyadams]

Thank you so much Greg - I would have never have spotted that! I have gone back over the file and every time 1.10, 2.10, 3.10 etc was used a " ' " was used rather than setting the decimal places to 2.

Thank you so much for all your help on this!!

[Greg M]

Hi again,

Many thanks for all of your feedback, and also for the Reputation increase - much appreciated!

You're very welcome - glad I was able to help.

Best regards,

Greg M