Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Linking graphs to columns of Data

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •