Automatically updated chats x values

**Beltzer71** · 11-19-2008, 02:56 AM

Hi

My problem: I have a lot of charts in one file. I have to update each one of them every month after i added monthly data.
Can the chart be changed so it automatically changes its range to include added data?

help will be appreciated.

**rwgrietveld** · 11-19-2008, 03:16 AM

Yes, This is easily done with 'dynamic ranges'. The internet (and this forum) is full of these formula's. They all look like:
=OFFSET($A$1,0,0,COUNTA(A:A),1) when there is no header and
=OFFSET($A$2,0,0,COUNTA(A:A)-1,1) when there is a header.

Can you adjust this for your data? If not please add a workbook and w'll do it for you.

**Beltzer71** · 11-19-2008, 03:52 AM

I can unfortunately not upload my worksheet, its huge.

But I've attached a document with similar data

As i put in data in the worksheet i want the graph to automatically adjust its x-range.

Thanks

**rwgrietveld** · 11-19-2008, 04:19 AM

Define two names:
XLABELS=OFFSET(Sheet1!$A$5,0,0,1,COUNTA(Sheet1!$6:$6))
VALUES=OFFSET(Sheet1!$A$6,0,0,1,COUNTA(Sheet1!$6:$6))
Make a clustered column graph. Select one of the bars. You'll see this appear
=SERIES(,Sheet1!$A$5:$M$5,Sheet1!$A$6:$M$6,1). Change this into
=SERIES(,example.xls!XLABELS,example.xls!VALUES,1)

**Beltzer71** · 11-19-2008, 04:39 AM

Ill rather get someone to do this, i dont have a clue how to do those things you talk about. thanks anyway.

**rwgrietveld** · 11-19-2008, 04:52 AM

What version of excel are you using? Please give it a go. It's not hard and this forum is for you to 'learn'. This is when you have Excel 2003:

Select A5:M6
Make a graph by Insert Chart, Column, Chart sub type: clustered column graph. Press Finish and the graph will appear.

Now we will define names:
Insert -> Name -> Define
XLABELS, refers to =OFFSET(Sheet1!$A$5,0,0,1,COUNTA(Sheet1!$6:$6)) Press OK
Insert -> Name -> Define
VALUES, refers to =OFFSET(Sheet1!$A$6,0,0,1,COUNTA(Sheet1!$6:$6)) Press OK

Press one of the bars in the graph and you'll see this: =SERIES(,Sheet1!$A$5:$M$5,Sheet1!$A$6:$M$6,1)
replace this with =SERIES(,example.xls!XLABELS,example.xls!VALUES,1)

Succes, another day lived, another day learned

**Beltzer71** · 11-19-2008, 05:30 AM

thanks a LOT!!! It worked. Now i must just try it on my "real" worksheet

**Beltzer71** · 11-19-2008, 05:41 AM

i have a lot of graphs on my worksheet, can i make use of different names for each and use the offset to just count from the one?

**rwgrietveld** · 11-19-2008, 06:44 AM

If the number of VALUES is equal for ALL, then yes.

**Andy Pope** · 11-19-2008, 06:45 AM

It's not clear from your example but if you wanted to plot data from row 7 and the labels remain in row 5 you can use this,

=OFFSET(XLABELS,2)

to create new named ranges as rwgrietveld has described.

**Beltzer71** · 11-19-2008, 07:18 AM

Originally Posted by rwgrietveld

If the number of VALUES is equal for ALL, then yes.

Yeah it is. Jan, Feb... with different data i want to plot per month, just another quick question. If i want to start with colom "B" do i just start with the reference in "B" and subtract 1 from the COUNTA value?

**Andy Pope** · 11-19-2008, 07:24 AM

Either use the offset argument

=OFFSET(Sheet1!$A$5,0,1,1,COUNTA(Sheet1!$6:$6)-1)

or adjust anchor reference

=OFFSET(Sheet1!$B$5,0,0,1,COUNTA(Sheet1!$6:$6)-1)

**Beltzer71** · 11-19-2008, 08:20 AM

Originally Posted by Andy Pope

Either use the offset argument

=OFFSET(Sheet1!$A$5,0,1,1,COUNTA(Sheet1!$6:$6)-1)

or adjust anchor reference

=OFFSET(Sheet1!$B$5,0,0,1,COUNTA(Sheet1!$6:$6)-1)

thanks, i must play around with this...

**Beltzer71** · 11-20-2008, 04:39 AM

I did this, the offset "range" changes when i look in the "XVALUES" defined name, but the chart does not update... Ive looked over and over but i cant find the fault...