• 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 a Dynamic Range Chart - Multiple Items

G

gumbo191

New Member
I have used the OFFSET formula in the name manager for charting ranges before but with this example I need to include more than just one row of data and the Offset only seems to work with one column or row at a time. I want my values in the line chart to stop when there is no data found not drop down to a zero value. I have included an example and would appreciate any guidance from the experts!
 

Attachments

  • Dynamic Sample.xlsx
    16.3 KB · Views: 4
Hope its same as your need.
In your file as i have noticed that you have selected range till column W with blank and cells value SPACE in it, that was the reason that lines were going to hug ZERO.
Attached file by me is dynamic which is using its range till data in Row 2 & Column B.
 

Attachments

  • Dynamic Sample - Sol.xlsx
    14.2 KB · Views: 4
I need to clarify, the blank cells will have formulas in them causing the lines from the chart to drop off when no data is present. I have modified the file accordingly to demonstrate the issue.
 

Attachments

  • Dynamic Sample.xlsx
    15.9 KB · Views: 6
If that's the thing than can you please tell me is there any possibility of getting your data in negative values.
 
Not sure what you are asking for, My goal here is to make the range dynamic so that the data being charted in the sample (through week 13) stops at 13 and when new data is added (week 14) the range automatically extends to find the new data.
 
No, I need the range to automatically update as new weeks are added to the data so I don't have to go back into the chart and re-define the new range of data. So when i refresh my data and add week 14, week 15, etc. the chart range adjust accordingly to the newly refreshed data. I have done this before on 1 data field using named ranges in the series field of the chart but never for multiple series.
 
Can you see mine as it is doing the same, If I add data in Wk14 in one 1 series than chart update for that particular series.

Regards,
 
Got it! I considered using the offset for each range I was just thinking there would be a solution for the entire range of data. Thanks for your help!
 
You must log in or register to reply here.
Back
Top