Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is my first post here, so excuse me if my etiquette is off, or if my question seems elementary - I am quite the Excel novice.

    I am trying to create some prototypes of a workbook with data and corresponding dynamic (one that updates when additional data is entered) charts. I have figured out how to create a dynamic chart with just two columns of data; I just can't get it to work with mulitple columns of data.

    On the attached, I've deleted the defined names I attempted to create to make the dynamic chart in Sheet 2. I am doing this for work (budget work, a new job for me), and my boss wanted me to create a couple of examples of how we can approach doing this before trying to tackle larger and more complex data sets.

    Thanks in advance.

    TT
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    In the attached version, I have created a custom VBA function:

    Code:
    Public Function LastRow(rng As Range) As Long
      LastRow = rng.Find(What:="*", _
    	SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End Function
    This is used in cell F1 on Sheet2 to return the row number of the last row with data:

    =LastRow(B:C)

    I have defined named ranges:

    Sheet2!ChartLabels: =OFFSET(Sheet2!$A$2,0,0,Sheet2!$F$1-1,1)
    Sheet2!ChartSeries1: =OFFSET(Sheet2!$B$2,0,0,Sheet2!$F$1-1,1)
    Sheet2!ChartSeries2: =OFFSET(Sheet2!$C$2,0,0,Sheet2!$F$1-1,1)

    and these are used for the X-values and for the Y-values of the two series in the chart, respectively.

    Because cell F2 uses a custom VBA function, users will have to enable macros when opening the workbook. Otherwise the formula won't work.
    Attached Files Attached Files

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='ttodd27' post='776479' date='22-May-2009 14:00']This is my first post here, so excuse me if my etiquette is off, or if my question seems elementary - I am quite the Excel novice.

    I am trying to create some prototypes of a workbook with data and corresponding dynamic (one that updates when additional data is entered) charts. I have figured out how to create a dynamic chart with just two columns of data; I just can't get it to work with mulitple columns of data.

    On the attached, I've deleted the defined names I attempted to create to make the dynamic chart in Sheet 2. I am doing this for work (budget work, a new job for me), and my boss wanted me to create a couple of examples of how we can approach doing this before trying to tackle larger and more complex data sets.

    Thanks in advance.

    TT[/quote]
    In case your user has an aversion to files containing code; the attached worksheet provides a codeless solution which is based on the assumption that there will always be a projected value if an actual exists.
    Attached Files Attached Files
    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='wdwells' post='776524' date='23-May-2009 01:41']In case your user has an aversion to files containing code; the attached worksheet provides a codeless solution which is based on the assumption that there will always be a projected value if an actual exists.[/quote]
    In this particular situation, it is unlikely that there will be an actual value in a row if there is no projected value, so using the Projected column to determine the number of rows to use will most probably work well.
    But in a more general situation, the last used row wouldn't necessarily be in the data for the first series...

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='776526' date='22-May-2009 19:51']In this particular situation, it is unlikely that there will be an actual value in a row if there is no projected value, so using the Projected column to determine the number of rows to use will most probably work well.
    But in a more general situation, the last used row wouldn't necessarily be in the data for the first series...[/quote]
    Of course you are right Hans (as always). This approach can be adapted to dynamically adjust the range to any one series, but it is a great deal more labour intensive than your User Defined Function. It is also worthy of note that the Master column (B in this case) must not have any blanks mixed in with the data.
    Regards
    Don

  6. #6
    New Lounger
    Join Date
    May 2009
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to both of you for the help!!

Posting Permissions

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