• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating Dynamic Chart

I want to create a dynamic chart wherein the chart area will only select the cells, wherever the data is, however if I add data in the blank cells, the chart would automatically select the newly added data too.
 

Attachments

  • Dynamic Chart.xlsx
    14.7 KB · Views: 4
I have attached a revised spreadsheet, wherein I have tried to create dynamic charts, but its not working, here data in cells S3:Y24 are pulled with a formula, hence wherever the data is not available its left blank, but when I am trying to create dynamic charts, its considering these formulas as value only.

Please help me with the same.
 

Attachments

  • Dynamic Charts Revised.xlsx
    23 KB · Views: 6
Hi

I think you are close.
On your chart series replace the highlighted range by the correct name manager reference and you will be ok. The sheet reference needs to stay.
View attachment 52105

Have a read on this. I'm sure you will get it.
https://chandoo.org/wp/2009/10/15/dynamic-chart-data-series/
I did that only, but the problem here is all the values in highlighted range are pulled from formula, that means, there is a formula in the highlighted range, and it is returning value, wherever it is matching the criteria, and leaving blank where criteria is not matching.

And also, I beleive Offset is not working properly, and it is considering the cells as blank where the formula is not pulling any value in highlighted area.
 
Try this in your offset formulae:
- use count, not counta: count will only consider number values, not text.
- remove the -1, because you are not counting the title.

Q? Could there be blanks in between the values? Or is it then "0"?
 
Try this in your offset formulae:
- use count, not counta: count will only consider number values, not text.
- remove the -1, because you are not counting the title.

Q? Could there be blanks in between the values? Or is it then "0"?
Thank you Very much, this was very helpful

One more question, can this be done by just selecting a specific range.
In this version, I have selected the whole column with offset function
 
I believe you can, as long as the range is "big" enough to hold the maximum of potential values you want to plot on the chart. In the tutorial on the blog you see Chandoo uses a specific range as well.
 
Just use Excel Table structure and/or PivotTable.

No need to maintain Named Ranges. It will auto adjust to include added data.
 

Attachments

  • Dynamic Chart.xlsx
    15.9 KB · Views: 13
Back
Top