Dynamic Range for Chart Source data

Could you post a workbook instead of a word file?

At first glance, it looks like you are on the right track, but do you realize you are missing a closing parentheses on your equation? As Jeff states, the workbook usually helps.

Also, it looks like your data starts at D10, but then you ask it to COUNTA for all of column D. If there is anything in D1:D9, those will get counted, too, and likely mess up your offset.

Pauley

Hi vlad23,

Sorry I got tied up yesterday and couldn't quite finish this up.

One of the draw backs to what you were attempting to use, what if you have less than 30 which in your case there are two.

See if this might be an option for you. Instead of 6 or so charts, what about one chart and you cycle the data through it by way of a drop down.

You'll see all data is in one column so enter new data at the end of the column and then hit the sort button. I think this is a better setup but I know it may not work for you. With the method you had you would have to build twelve different named ranges, but let me say, I couldn't think of a better way.

Let me know what you think

No problem just give me some time.

Here are the seperate charts but I just built three. Hopefully you can figure the rest.

As far as the sort button, aftering adding new data to the bottom of column A, B, and C, yes select the sort button. Works for me.

Did you enable macros? Did you download the file to your desktop or did you just open from the thread? I don't think that would make a difference but just asking.

Is this 2003 you are working with?

And you did not change anything within the file? Anyway, I thought you were going to go with the seperate charts method?

Take a google search for that error number and see what it comes up with. That's what I would have to do because I sure don't know what that code is describing.

I'm not quite following that description.

Are you saying, in one chart have the set of values from (A, AA, and AAA)?

Sorry, I have not had access to this site for the last 6 hours or so.

I updated the file at post #12

In order to figure this out it is imperative you understand the offset function. You can find info in the Excel Help or even study the named ranges in the attached workbook.

Here is a scenario which hopefully helps makes sense of the Offset function.

Let's go with some days of the week in column A sorted A-Z with a total in column B. We are going to sum all values relating to "Tue". (Range >> A1:B9)

Day Total
Mon 1
Mon 2
Thu 3
Thu 4
Tue 5
Tue 6
Wed 7
Wed 8

=OFFSET(reference,rows,cols,[height],[width]) >> There are 5 arguments to the Offset function, but the last 2 are optional that is why they have [] around them.

Reference: Where do you want to start? In this case, we'll start in $A$1. Lock it down with absolute references.

Row: How many rows down from A2 do you have to go to find the first "Tue"? We will use the Match function to find the position from A2 where the first "Tue" starts. MATCH("Tue",$A$2:$A$9,0) >> result is 5

Cols: In this case we are going to sum column B so we go over 1 column.

Height: How many "Tue" do we find? We use the Countif function to find out how many "Tue" there are. COUNTIF($A$2:$A$9,"Tue"))) >> result 2

Width: We are not going to use this so we just leave it off.

The answer is 11 (5 + 6) and here is how the formula breaks down.

=SUM(OFFSET($A$1,MATCH("Tue",$A$2:$A$9,0),1,COUNTIF($A$2:$A$9,"Tue")))
=SUM(OFFSET($A$1,5,1,COUNTIF($A$2:$A$9,"Tue")))
=SUM(OFFSET($A$1,5,1,2))
=SUM($B$6:$B$7)
=11

In this formula I hard coded "Tue" but you could point to a cell such as D1. Also, Offset will not return anything as a worksheet function so it requires a wrapper function so we use Sum. In the named manager we do not need a wrapper function as Offset can return a range.

I may have left something out and if so just ask, but my hope is you understand the Offset function enough now to correct the function from your last post.