[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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?
0
sch21c
Asked:
sch21c
  • 3
  • 3
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
sch21cAuthor Commented:
Sorry for the delay in responding, I was traveling.  Thanks for the help, problem solved!  This service is great.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now