Streamlining a series of charts with ever changing data ranges?

Sounds like you want to work with dynamic named ranges. See here...

http://www.contextures.com/xlNames01.html#Dynamic

Once you have those, then you place the named range into your series instead of a set range. Note: You must include the sheet name along with the defined name (i.e. sheet1!Bob)

I don't believe it can be done the way you are suggesting. I'll play around with it.

Once you set up a series name, it automatically updates whenever you add data. I am attaching a simple example.

I set up "Bob" as the first series
=OFFSET(Sheet1!$B$2,0,0,1, COUNT(Sheet1!$2:$2))

MyDate is then
=OFFSET(Bob,-1,0)

Mary is
=OFFSET(Bob, 1,0)

Larry is
=OFFSET(Bob, 2,0)
and so on.
As you add data to Bob's row, the graph will add it automatically.

ChemistB,
Ok, I'm learning by editing things in your sheet. I add a column for Oct and add data below it, nothing happens to the charts until I click Calculate Now and then they are updated. I'm not sure what the $2:$2 values are for in the COUNT routine? I understand that it makes excel count the number of cells in the 2nd row that have a value in them, but is it actually saying to count all cells with values from Row2 to Row2?

My second question pertains to omitting the first column or two with data. In the series BOB, I changed $B$2 to $C$2 and now when I hit Calculate Now, the Jan values are dropped from all charts, but because the COUNT still sees 10 cells with values in Row2, it starts from C and goes to L, which has no data in it. How do I have it COUNT Row2 starting from Column3, instead of Column2?
Thx,
Brett

Also, I have random cells for each data set that have empty cells, so it may be that COUNT would actually result in different numbers if ran on all data sets. Will this wreak havoc with the charts?

Let's start with your final question. Yes, that will cause havoc with the charts. Not to worry, we can use a different method rather than COUNT. It might be easier if you uploaded an example of how your data would be set up so my example closely match what you really want to do instead of trying to interpret and modify everything.

Q1. Yes, it is saying to count all the numbers in row 2 assumes that every cell has a number in it. The COUNT($2:$2) is nested within the OFFSET function in the 5th argument which dictates how wide (how many columns) the range will be. Sounds like your sheet is set up for "Manual Calculation" If you want things to update automatically, Formulas Tab on ribbon > "Calculation Options"> "Automatic"

Q2: How do I set the formula to start from column 3 instead of 2. Change
=OFFSET(Sheet1!$B$2,0,0,1, COUNT(Sheet1!$2:$2))
to
=OFFSET(Sheet1!$C$2,0,0,1, COUNT(Sheet1!$2:$2))
or
=OFFSET(Sheet1!$B$2,0,1,1, COUNT(Sheet1!$2:$2))

To upload an example spreadsheet. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Okay, so you will be adding data down the columns. It looks like the best column to base your dynamic range on would be Column B which has your date. Looks like you add dates as you add data. It doesn't look like you skip any rows in the date column. Is that true?
If so, set MyDate = OFFSET(Fixed!$B$3, 0,0, COUNT($B:$B),1)

BW05T (can't have spaces or dashes in defined names) is offset 6 columns so
=OFFSET(MyDate,0,6)
Note: also can't have a defined name which is the same as a cell name and, for example BW05, so maybe start those named with an underscore _BW05
=OFFSET(MyDate,0,5)

I then created some charts on the Fixed sheet (at about row 650) for BW-05 and BW-05T) using the technique I described in Post #2.
Select the chart, then go to the Design Tab and > Select Data. Insert the dynamic names as shown in Post 2.
Questions?

There are all sorts of ways to come up with dynamic named ranges.

Let's say that you only want the last 100 data points so if you add 5 data points to the end, 5 data points drop off the beginning. You'd use this formula

MyDate = OFFSET(Fixed!$B$3, COUNT($B:$B)-100,0, COUNT($B:$B),1)

Lets' say you don't want to start until some indefinite point where the data begins, for example in Col H, we want to start at H672 and end at H6172 (last number)

BW05T =INDEX($H:$H, MATCH(TRUE, ISNUMBER($H:$H),0)):INDEX($H:$H, MATCH(9E+99, $H:$H))

Is there a logical reason why you want to start at point A and end at point B (first number/last number, this year/last year, etc.")?
If there is no logic to your madness (arbitrarily picking this is a good point to start or end), then we can go with defined names that refer to specific cells but that will use INDIRECT and might use up a lot of memory and slow your system down. (Even my formulas may end up slowing down the calculations with 50 different series).

If you do need to create series containing special beginning and end points, say Column L putting beginning row in N1 and end row in M1

NewRng =INDIRECT("Fixed!$L" & N1 & ":$L" & M1)

Glad to help. Please go to post #1 and choose "Thread tools" > Mark as solved.