Linking graphs to columns of Data

[jrf001]

Hi Guys/Girls,
I have columns of Data. Months (March through to Dec), with Data under each heading (about 10 rows of Data). I have made a Graph (Bar Graph) out of March's Data, but as new data is entered into April, May, June columns etc, I want the Graph to refer to the new months data, and subsequent months as the year goes on. I have seen Graphs that you click on and it changes the data. i am not sure if thats what I need. Any quick ideas.

cheers Jason

[Mark W.]

Suppose Sheet1!A1:A11 contains...

{"Mar","Apr","May"
;991,925,267
;633,720,538
;38,651,296
;904,814,326
;784,216,558
;918,372,876
;842,596,43
;862,607,708
;61,653,206
;758,880,489}

and Sheet1!C1:J1 contains...

{"Jun","Jul","Aug","Sep","Oct","Nov","Dec"}


Insert a Defined Name, "Sheet1!X_Axis_Labels", that refers to...

=OFFSET(Sheet1!$A$1,,,,MAX(COLUMN(Sheet1!$A$2:$J$11)*ISNUMBER(Sheet1!$A$2:$J$11)))

...and insert a Defined Name, "Sheet1!Series1", that refers to....

=OFFSET(Sheet1!X_Axis_Labels,1,)

...a Defined Name, "Sheet1!Series2", that refers to...

=OFFSET(Sheet1!X_Axis_Labels,2,)


Repeat this pattern of defined names for...

=OFFSET(Sheet1!X_Axis_Labels,3,)
=OFFSET(Sheet1!X_Axis_Labels,4,)
=OFFSET(Sheet1!X_Axis_Labels,5,)
=OFFSET(Sheet1!X_Axis_Labels,6,)
=OFFSET(Sheet1!X_Axis_Labels,7,)
=OFFSET(Sheet1!X_Axis_Labels,8,)
=OFFSET(Sheet1!X_Axis_Labels,9,)
=OFFSET(Sheet1!X_Axis_Labels,10,)

Create blank Bar (or Column?) chart by choosing the Chart Wizard with an empty cell selected. Paste (or type) each the following SERIES formulas into the formula bar...

=SERIES(,Book1!X_Axis_Labels,Sheet1!Series1,1)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series2,2)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series3,3)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series4,4)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series5,5)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series6,6)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series7,7)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series8,8)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series9,9)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series10,10)

Now as you add data your chart's X-axis will automatically expand to accommodate the new data.

[jrf001]

Hi Mark,
Can you explain the last step to me again. I am having trouble with the graph part.

cheers Jason

On 2002-03-21 15:41, Mark W. wrote:
Suppose Sheet1!A1:A11 contains...

{"Mar","Apr","May"
;991,925,267
;633,720,538
;38,651,296
;904,814,326
;784,216,558
;918,372,876
;842,596,43
;862,607,708
;61,653,206
;758,880,489}

and Sheet1!C1:J1 contains...

{"Jun","Jul","Aug","Sep","Oct","Nov","Dec"}


Insert a Defined Name, "Sheet1!X_Axis_Labels", that refers to...

=OFFSET(Sheet1!$A$1,,,,MAX(COLUMN(Sheet1!$A$2:$J$11)*ISNUMBER(Sheet1!$A$2:$J$11)))

...and insert a Defined Name, "Sheet1!Series1", that refers to....

=OFFSET(Sheet1!X_Axis_Labels,1,)

...a Defined Name, "Sheet1!Series2", that refers to...

=OFFSET(Sheet1!X_Axis_Labels,2,)


Repeat this pattern of defined names for...

=OFFSET(Sheet1!X_Axis_Labels,3,)
=OFFSET(Sheet1!X_Axis_Labels,4,)
=OFFSET(Sheet1!X_Axis_Labels,5,)
=OFFSET(Sheet1!X_Axis_Labels,6,)
=OFFSET(Sheet1!X_Axis_Labels,7,)
=OFFSET(Sheet1!X_Axis_Labels,8,)
=OFFSET(Sheet1!X_Axis_Labels,9,)
=OFFSET(Sheet1!X_Axis_Labels,10,)

Create blank Bar (or Column?) chart by choosing the Chart Wizard with an empty cell selected. Paste (or type) each the following SERIES formulas into the formula bar...

=SERIES(,Book1!X_Axis_Labels,Sheet1!Series1,1)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series2,2)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series3,3)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series4,4)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series5,5)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series6,6)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series7,7)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series8,8)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series9,9)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series10,10)

Now as you add data your chart's X-axis will automatically expand to accommodate the new data.
[/quote]

[Mark W.]

Create blank Bar (or Column?) chart by choosing the Chart Wizard with an empty cell selected. Paste (or type) each the following SERIES formulas into the formula bar...

=SERIES(,Book1!X_Axis_Labels,Sheet1!Series1,1)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series2,2) ...

Selecting an empty cell and choosing the Chart wizard will create a formatted chart without any series. Series can be added by typing or pasting them in the formula bar.

You're undoubtedly struggling because in my rush I posted formulas that included a globally named x-axis label (Book1!X_Axis_Labels instead of Sheet1!X_Axis_Labels). Sorry, for the confusion that I may have caused. The proper SERIES functions for my example should be...

=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series1,1)
=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series2,2)
=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series3,3)
=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series4,4)
=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series5,5)
=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series6,6)
=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series7,7)
=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series8,8)
=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series9,9)
=SERIES(,Sheet1!X_Axis_Labels,Sheet1!Series10,10)

[Dave Hawley]

Hi Jason


I think you will find some examples here the will help:
http://www.ozgrid.com/Excel/ChartTips.htm

There is also a download that will help here:
http://www.ozgrid.com/download/default.htm
called : ChartByMonth.zip