# Thread: Dynamic Chart with Multiple Columns of Data

1. 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.

TT

2. 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.

3. [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.

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.

4. [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. [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.

6. 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
•