I have a chart with two lines; one to represent when items are scheduled to be done and the second line to indicate when they were actually done. Both lines pull from a table that tracks scheduled and completed items, by month, with each column representing a different month. The chart plots when scheduled items should be done for the next 15 months. I only show the completed items from the beginning through the current month/date. The problem is, I have to go in and manually adjust, using Select Data to change the range by one month (i.e. one column) every month.


Is there away to make the address / range dynamic? Thank you.

Could you please post a sample sheet ( no pics please). Thx

EDIT perhaps this link will get you on track

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

You will see in my spreadsheet I have a chart with two lines of data; one for "Scheduled" and one for "Completed". The Scheduled data uses all of the values from B7:Q7, which goes to January 2015. For the Completed data, I only show from the beginning (B8) until the current month, in this case E8. My problem is that each month I need to go to the graph, click on "Select Data" and increment the range for "Completed" by one column, so next month, I will change it to be B8:F8 and then B8:G8, etc. I have many of these so it is time consuming and I have to remember to do it and then hope I don't make any mistakes.
Ideally, I would be able to use current date logic and make the range end at something less than or equal to today's date.
Thanks in advance for your help.
Steve

Attached Files

Sample Variable or Condiitional Data Range.xlsx (17.1 KB, 14 views)

Have you checked the provided link?

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I haven't attempted it yet, but after reading the dynamic charting techniques in the link you provided, I'm pretty sure I'll be able to do what I need to do. Thank you.

I was able to use the Dynamic Names feature to accomplish what I need to do, but since I have multiple charts, I'm trying to avoid having to create a separate Dynamic Name that corresponds to each chart.

I'd like to be able to use the following statement in the Series Values section of the data for my chart, but it tells me the formula contains an error:

=Sheet1!$B$11:OFFSET(Sheet1!$B$11,0,Sheet1!$B$13)

Is there a chance I'm just using the wrong syntax or am I not allowed to use OFFSET to define the end range of the Series value?
Thank you.

I just tried the following formula in a test worksheet and it seemed to work:So apparently I can use a value in the range as one of the offsets. Guess that's not the problem.

I notice in your case your range runs across a single row rather than down a column. I suppose you meant it to be that way?

Yes, I can use it in a SUM statement, so I assumed the syntax is valid, but it won't let me put that same syntax in the Series Value box when specifying what data to use for my chart.

And yes, my example is intentionally meant to go across a single row. I have many single rows, each with their own chart. If I could use the statement with an OFFSET in the Series Values, I could reuse it for every chart. If I can't, the it looks like I will have to create a separate range name for each chart (18 of them), which is doable, but I'm trying to avoid it if it isn't necessary. Thanks.