1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'Discuss Data Visualizations and Charting' started by msharma864512, May 11, 2018.

  1. msharma864512

    msharma864512 Member

    Messages:
    101
    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.

    Attached Files:

  2. msharma864512

    msharma864512 Member

    Messages:
    101
    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.

    Attached Files:

  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    777
  4. msharma864512

    msharma864512 Member

    Messages:
    101
    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.
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    777
    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"?
  6. msharma864512

    msharma864512 Member

    Messages:
    101
    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
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    777
    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.
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    4,929
    Just use Excel Table structure and/or PivotTable.

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

    Attached Files:

  9. msharma864512

    msharma864512 Member

    Messages:
    101
    Thank you Chihro, it was great, but how it was done

Share This Page