Dynamic Line Chart

I've searched the forums and have been unable to accomplish my tasks based on what I've found.

So I'm trying to create a dynamic line chart that updates every month. Attached is a sample workbook. In the example, I want to be able to add the month of September along with its sals and profit, and have the chart update automatically.

I've seen some examples using the offset function, but I can't seem to get it to work.

Thanks in advance!

You missed the worksheet!

Click on your sales line on the graph and change the formula to :-

=SERIES(Sheet1!$B$1,Example.xlsx!Date,Example.xlsx!Sales,1)

same with the Profit and it should do what you want

here is the modified file

Also, with your defined names, once you've defined "date" you can use that with OFFSET to define the others, i.e.
Profit = OFFSET(Date,0,2)
Much easier on the eyes and brain.

Its your file with the changes, I re-downloaded it and it works fine, can any body else verify it since i am using 2010, but there shouldnt be any compatibility problems with 2007

Robert, I downloaded your file and the data series seemed to be linked to a document on the C drive. I fixed it and attached but am getting an "illegal reference in your formula"(even though it works). Every time I tried uploading again, it became corrupted. Weird.
Here's the log

Please Login or Register  to view this content.

Hambone, in answer to your question,
If you go to "Defined Names" or "Name Manager" you can replace the two following name formulas (Note, because of problems with Squiggler's file, I substituted my own and used my formulas).
Sales = OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)
Profit =OFFSET(Sheet1!$C$2,0,0,COUNTA(Sheet1!$A:$A)-1)

with
Sales = OFFSET(Date,0,1) Offsetting the date series by 1 column
Profit = OFFSET (Date,0,2) Offsetting the date series by 2 columns.

lol, thanks I wonder if it is Excel 2010 that is the problem?

(BTW I am not Robert he is the person who I quoted!)

First, I defined the names. Formula Tab>Name Manager starting with Date
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$500)) which sets the range starting at A2, offsetting 0 rows and 0 columns with a height of whatever the count is from A2 to A500. Note, you cannot skip rows or the chart won't work properly.
Next two names use the offset again to offset the defined name Date as follows
Sales = OFFSET(Date,0,1) Offset date by 0 rows and 1 column and similarly with Profit

Next, right clicking on the chart and Select Data, Edit the x axis to = Sheet1!Date
Edit Sales =Sheet1!Sales and similarly for profit.
Note: After doing so, if you go back and look, Excel changes it to the workbook name (probably because it's a workbook defined name) like so Sales = Example!Sales.
Then as you add more dates, the Date named range will expand which will expand Sales and Profit and these are directly linked to your chart. Hope that helped.

Thanks ChemistB. I was able to walk through your instructions perfectly. Very helpful. I hate to add an additional requirement, but suppose I wanted to graph only the last 12 months, as oppossed to simply adding on the additional month. How would I make that happen? Also, some entries will not have a total of 12 months, so I would like for this formula to be able to handle both scenarios.

In other words, if there are 12 or more months, I would like to chart only the last 12. If there are fewer than 12 months, than I want to chart all the months.

As always, I really appreciate the help.

You need to go back to your Defined Name "Date" Since the other two names are dependent on that one, that's the only one you need to change. This formula

Please Login or Register  to view this content.

will offset the rows by whatever is larger 0 or COUNTA(A2:A500)-12. It also automatically makes your range height (rows) = 12. Compare it to the original formula.
If you don't want the height to always be 12, you could use this formula instead

Please Login or Register  to view this content.

Let me know if you need help working through the logic.