Using VBA in Excel 95 to add data points to a graph

I have an existing graph that gets updated daily with new data.  The data resides in another sheet in the workbook.  I need to have the graph updated with the 2 relevant data points (and the X axis label) via a macro, so the end user does not have to use the Insert/New Data... menu item.  How do I get the appropriate data points in the last row added to the graph?
sch21cAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

calacucciaCommented:
Hi sch21c,

You might need a dynamic range to refer to.

The following will work if the column containing the data has no other used rows below.

Supposing the X-labels are in column A, the data in columns B and C , and the names for X-label and data series in row 1, all these being on sheet1:

Define the X-labels as a dynamic range:

Go to Menu Bar/Insert/Name/Define

Type the name you want to give in 'Names in Workbook' (e.g. Time), now go to the box 'refers to' and type:
=OFFSET(Sheet1!$A$1;1;0;COUNTA(Sheet1!$A:$A)-1;1)
This will create a dynamic range containing all non-empty cells from line 2 on in column A.

WATCH OUT:You might have to use the "," instead of the ";" notation !!!!

Do for columns B and C something similar
Column B, define a name (e.g. value1) and type in refers to:
=OFFSET(Sheet1!$B$1;1;0;COUNTA(Sheet1!$A:$A)-1;1)
Column C, define a name (e.g. value2) and type in refers to;
=OFFSET(Sheet1!$C$1;1;0;COUNTA(Sheet1!$A:$A)-1;1)

Now, go to your graph, on the other sheet, click on one of both data series you want to be updated automatically, and the refernce you had before, should look sometihng like:

=SERIES(Sheet1!$B$1;Sheet1!$A$2:$A$13;Sheet1!$B$2:$B$13;1)

Replace this reference by:
=SERIES(Sheet1!$B$1;ThisBook!Time;ThisBook!Value1;1)

Where "ThisBook" is the name of your file/workbook, once again, "," might be necessary.

Do the same thing for the second data series (but use value2).

Now add a few lines on the data sheet and look at the graph, it should have changed.

Hope this helps,

Calacuccia
0
sch21cAuthor Commented:
OK, I see what you are doing.  But, unfortunatly, there are formulae in the empty cells below the last row of actual data, as some of the data displayed in the chart is calculated and used in another pie chart in the same workbook.  Any ideas on how to make this work if there is formulae, but zero value, cells below the last data line?
0
sch21cAuthor Commented:
Adjusted points to 125
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

antratCommented:
Hi sch21c

using the Dynamic range option change the COUNTA part to COUNT then your range will only count numbers and not text.

Or have your formulas return #N/A if they are false that way Excel will not plot them. IE:

=If(A1>5,100,#N/A) notice there are no quotes around the #N/A.

good luck

antrat
0
calacucciaCommented:
Hi sch21c

If you use the following reference formula for the 3 names, this will create three dynamic ranges going from line 2 to the last line before the first blank in the column:

(This is copy and paste from Ture's Comment in a PAQ
##### Formula by Ture ##############
=OFFSET(Sheet1!$A$2;0;0;MIN(IF(ISBLANK(Sheet1!$A$2:$A$65536);ROW(Sheet1!$A$2:$A$65536);""))-2;1)

For column B, the same formula, but slightly modified to start from cell $B$2:
=OFFSET(Sheet1!$B$2;0;0;MIN(IF(ISBLANK(Sheet1!$A$2:$A$65536);ROW(Sheet1!$A$2:$A$65536);""))-2;1)

For column C:
=OFFSET(Sheet1!$C$2;0;0;MIN(IF(ISBLANK(Sheet1!$A$2:$A$65536);ROW(Sheet1!$A$2:$A$65536);""))-2;1)

This should be working for you,


Good Luck

Calacuccia
0
calacucciaCommented:
Hi sch21c,

Did our last commments help you ?

Please leave a comment, and if you were helped, accept one of our comments above.

Calacuccia
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sch21cAuthor Commented:
Sorry for the delay in responding, I was traveling.  Thanks for the help, problem solved!  This service is great.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.