Dynamically Expand Range to Create Chart

**alis88** · 01-21-2009, 09:01 AM

Dear all

Please see the attached example.

I have data listed as shown, but need the chart to automatically update whenever a new sales figure is added to the next month.

I know you have to use the OFFSET to create dynamic ranges but I can't get it to work horizontally, only if my data is laid out vertically.

Please can you help?

Thanks

Alis

**sweep** · 01-21-2009, 09:24 AM

Hi,
Your data range can be defined like this:

=OFFSET(Sheet1!$B$3,0,0,1,COUNTA(Sheet1!$B$3:$AA$3))

And the labels like this

=OFFSET(Sheet1!$B$2,0,0,1,COUNTA(Sheet1!$B$2:$AA$2))

Please see attachment

**alis88** · 01-21-2009, 10:20 AM

Thank you so much!

Alis

**shawn22** · 07-04-2011, 06:31 PM

Originally Posted by sweep

Hi,
Your data range can be defined like this:

=OFFSET(Sheet1!$B$3,0,0,1,COUNTA(Sheet1!$B$3:$AA$3))

And the labels like this

=OFFSET(Sheet1!$B$2,0,0,1,COUNTA(Sheet1!$B$2:$AA$2))

Please see attachment

Hey there, Well I am a beginner so this question might seem stupid...I downloaded the excel shet but could not find this formula...where do you set or enter it?
thanks in advance...

**jeffreybrown** · 07-04-2011, 07:14 PM

Hello & Welcome to the Board,

Look in the name manager...these are named ranges

One is called months and the other sales

**ron2k_1** · 07-04-2011, 07:23 PM

Originally Posted by shawn22

Hey there, Well I am a beginner so this question might seem stupid...I downloaded the excel shet but could not find this formula...where do you set or enter it?
thanks in advance...

There is no formula anywhere. The idea was to use named ranges, and in the reference to the named ranges is where you use the formulas provided. Then you edit the data series of the chart to refer to the dynamic named ranges.

The attached does not fully reflect that as Sweep probably wanted to leave some fun for the OP. The named ranges for Months included up column K as well, so I corrected that and included up col AA similar to the sales range.