+ Reply to Thread
Results 1 to 5 of 5

Update Chart & Blank Rows

  1. #1
    Registered User
    Join Date
    07-18-2007
    Location
    Joliet, Illinois
    Posts
    30

    Update Chart & Blank Rows

    I have attached a small portion of a file that I am working on. It is a multi-year income statement that I have created a chart for.

    Two questions please....

    #1 Since the information that the chart is pulling from has a blank row in it (row 13), how do I get rid of it in the chart?

    #2 What do I need to differently to make the chart update automatically when the number change in the statement?

    It seems confusing to have revenue and expenses in the same chart. I'm thinking of maybe two. One to show yearly differences in total revenue, total expenses and net operating and one to show yearly department expenses differences.

    Okay maybe that was three questions and they shouldn't all be in the same post.

    Anything that anyone can contribute would be most appreciated.

    D
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi D,

    1) It appears that the blank row is only there for the visual impact of white space, this can be achieved w/o the blank row by deleting row 13 & doubling the height of the new row 13 which will be the "Total Admin depart". Your text is aligned at the bottom of the cells so the visual appearance of the data is the same as having two rows but no blank will appear in the chart.

    2) Your example file has a few errors in the series definitions which may be why numbers don't seem to be changing eg:
    -"series 2002" doesn't show any data because of this "ref#" error.
    =SERIES('5 Yr Comp Summary Inc Stmt (2)'!$J$7,,'5 Yr Comp Summary Inc Stmt (2)'!#REF!,6)
    - other series link to the wrong name/data. To see this easily:
    *change the "zoom setting" under View - Zoom to show the whole sheet & chart (eg 60%)
    *click on one of the columns. This will put the series formula in the Formula Bar & put coloured borders around the associated cells on the work sheet. You can click & drag these coloured borders to correct the series formulae.
    *Repeat the above for all of the series.
    3?) Yes, it can be good to separate a summary chart from the details this allows for a better scale to be applied to each chart. Rather than creating new charts from scratch I suggest correcting your existing chart so it has all the series, right click on the "chart area" - Copy, right click a cell elsewhere on the sheet -Paste. Then use this copy of your chart to delete series/change the layout until you like what you see.

    btw, Jon Peltier has a fantastic set of charting links on his site:

    http://www.peltiertech.com/Excel/Charts/ChartIndex.html
    including a link to Debra's dynamic chart explanation at the top of this page:
    http://www.peltiertech.com/Excel/Cha...hartLinks.html

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  3. #3
    Registered User
    Join Date
    07-18-2007
    Location
    Joliet, Illinois
    Posts
    30
    Rob,

    I appreciate you taking the time to look over my problem.

    #1 I like your suggestion and have taken that approach in the past. Don't know why it didn't come to mind this time. It makes sense.

    #2 I re-did the series a couple of time - oops, still not right. It kept getting mixed up. Thanks for the catch. I will make the corrections the way you suggested, sounds easier than the approach I was taking. It will probably solve the problem.

    #3 Two separate charts is also what I was thinking. By trying to get all of that on one chart I was finding that it had to be so large so you could read it tha it wouldn't fit on a piece of paper.

    I will check out your suggested link. I definately need a few self-help instructions.

    Thank you for your thorough reply. It is very much appreciated.

    I will reply back after I tackle the chart!

    Thanks again,
    D

  4. #4
    Registered User
    Join Date
    07-18-2007
    Location
    Joliet, Illinois
    Posts
    30
    Everything seems to be working great.

    I have corrected the series, got rid of the blank line, copied chart and changed data and now when the numbers change the charts change.

    I know I have more to learn but so far so good.

    Thanks for your expertise.

    D

  5. #5
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243
    hi D,
    Pleased I could help - thanks for the feedback :-)

    Rob

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1