Solved

automated chart with macro

Posted on 2000-03-22
20
534 Views
Last Modified: 2010-05-02
You will be my lifesaver if you know how to make an automatic updated chart with macro.  My problem is that I have date listed in my worksheet already and the data for each date will be enter daily.  If I simply use "shift+end+arrow" to highlight the whole table, then the chart will look odd because of missing data.  My guessing is that I still need to use the macro to make the chart and then modify the code. But how????

Thanks a lot
0
Comment
Question by:lisaswu
  • 9
  • 6
  • 4
  • +1
20 Comments
 
LVL 28

Expert Comment

by:AzraSound
Comment Utility
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Hi Lisaswu,

I've got a rather simple, maybe even no VB requiring solution.

Imagine these data:

The date are listed in column A, of sheet1, with a column header on row 1 (Cell A1), composing an unknown number of lines, say 100 for the example. The corresponding data will be in columns B and C, with each of course a column header on row 1.

Now, in your file, on sheet1, do the following steps:

1) Insert/Name/Define
2) In the 'Names in workbook' box, type "MyData" (without quotation marks)
3) In the 'refers to' box, type this formula:  *
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B:$B),3)
4) Click on 'Add' button
5) Insert/Chart which will launch the chart wizard to create your new, dynamic chart
6) Choose the type of chart you'd like in step 1 of 4
7) In step 2, go to Tab 'Data Range' and type in the 'Data Range' box "MyData" (without quotation marks)
8) In steps 3 and 4, do your usual stuff

Now the graph is created, and linked to a dynamic range, called "MyData".

* The offset function creates a range of cells starting from cell A1, moving 0 cells to the right and down, with 3  columns (A, B and C) and CountA(Sheet1!$B:$B) rows, which is exactly the number of rows in column B containing non-empty values, in other words, the number of data entered up to today.

The only problem with this is, that the chart once created forgets its link to a named (dynamic) range, and copies the corresponding range to its Data Source box.

So that's why to update the chart, you will have to tell it that it's linked to "MyData".

That can be done by a simple 'Update' command button, linked to a liitle macro.

To create the comand button, go to View/Toolbars/Forms, and drag the command button onto your chart sheet (if you use chart sheets, which I recommend).

Normally, an 'Assign Macro' dialog will pop up now (if not, right-click on button, and select 'Assign Macro'). Click on the 'New' button, and insert the following line of code, between the Sub calll end End Sub statement (probably, the proposed name will be Button1_Click()

ActiveChart.SetSourceData Source:=Range("MyData"), PlotBy:=ActiveChart.PlotBy

If you want to call the chart by its name, use the charts name between Charts("ChartName") as in this example

Charts("ChartName").SetSourceData Source:=Range("MyData"), PlotBy:=Charts("ChartData").PlotBy

Hope this helps

Calacuccia
0
 

Author Comment

by:lisaswu
Comment Utility
Thanks a lot, Calacuccia.  Your solution works so good.  However, I have problem to apply it on my project, because those cell contaning formula,i.e. they are not non-empty values. I should have mentioned it in my question. I tried to do it with my project several times, it just doesn't come out the way I want.  
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Lisaswu,

Do you happen to have any columns without formula's ?

If not, we could find another way to make this work..

But I'll need to know how the lines without data look.

Calacuccia
0
 

Author Comment

by:lisaswu
Comment Utility
Thanks a lot, Calacuccia.  Your solution works so good.  However, I have problem to apply it on my project, because those cell contaning formula,i.e. they are not non-empty values. I should have mentioned it in my question. I tried to do it with my project several times, it just doesn't come out the way I want.  
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
One way to solve this would be to use a formula 'Refers to' like
=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$B:$B,">0"),3)
as in my previous comment, but now the function Countif will count all cells with numeric result greater than 0, so all empty and 0 containing cells are not counted. As the column header should be included (text is noy counted by this formula, you'lll actually have to use:

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$B:$B,">0")+1,3)

Calacuccia
0
 

Author Comment

by:lisaswu
Comment Utility
The data in the table is based upon the data from another table. For example in cell E20 I enter formula like this
=IF((Data!$DI482:$DI512)=MOI!$A20,AVERAGE(Data!DN482:DN512)).
Because the data in range DN482:DN512 is not updated, i.e. they are
empty value, the error sign :#DIV/0! will come out.  Therefore, I make
another table and input formula like this :=IF(ISERROR(E20),"", E20), so
the cell with non updated entry will look empty.  I thought this will
trick MS chart wizard, but it shows "reference is invalid". I even tried to change the formula like this :=IF(ISERROR(E20),"-1", E20), it still says the reference is invalid when I tried to plot the chart with the formula you just gave me=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$B:$B,">0")+1,3).

 
0
 

Author Comment

by:lisaswu
Comment Utility
The data in the table is based upon the data from another table. For example in cell E20 I enter formula like this
=IF((Data!$DI482:$DI512)=MOI!$A20,AVERAGE(Data!DN482:DN512)).
Because the data in range DN482:DN512 is not updated, i.e. they are
empty value, the error sign :#DIV/0! will come out.  Therefore, I make
another table and input formula like this :=IF(ISERROR(E20),"", E20), so
the cell with non updated entry will look empty.  I thought this will
trick MS chart wizard, but it shows "reference is invalid". I even tried to change the formula like this :=IF(ISERROR(E20),"-1", E20), it still says the reference is invalid when I tried to plot the chart with the formula you just gave me=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$B:$B,">0")+1,3).

 
0
 

Author Comment

by:lisaswu
Comment Utility
The data in the table is based upon the data from another table. For example in cell E20 I enter formula like this
=IF((Data!$DI482:$DI512)=MOI!$A20,AVERAGE(Data!DN482:DN512)).
Because the data in range DN482:DN512 is not updated, i.e. they are
empty value, the error sign :#DIV/0! will come out.  Therefore, I make
another table and input formula like this :=IF(ISERROR(E20),"", E20), so
the cell with non updated entry will look empty.  I thought this will
trick MS chart wizard, but it shows "reference is invalid". I even tried to change the formula like this :=IF(ISERROR(E20),"-1", E20), it still says the reference is invalid when I tried to plot the chart with the formula you just gave me=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$B:$B,">0")+1,3).

 
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
Hi Lisaswu,

Have you tried to adapt the formula to look at the right column for its count ?

As it is now, it looks at column B, so doing some things on columns DI & E won't have effect on the formula.

=OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$E:$E,">0")+1,3)

will return a range of 3 columns (A, B and C), and the number of rows corresponding to the (count of cells > 0 in column E) + 1.

Is that what you tried ?

If not, could you paste the formula used, the range it should return...

Calacuccia
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Expert Comment

by:shapeless
Comment Utility
Hi Lisa,

When you said:

"If I simply use "shift+end+arrow" to highlight the whole table, then the chart will look odd because of missing data."

does it means that some of the cells are empty, without any data ?
0
 

Author Comment

by:lisaswu
Comment Utility
Hi Calacuccia,
Yes, I did try to adapt the formula to look at the right column for its count.  In fact, I even try to copy and past special (value) the table to a new worksheet and then plot the chart. It works perfectly.  I am almost positive this method doesn't work for my project is because MS recognize each cell with its formula, not the way it shows on the table.  Do you have better idea?  I am very grateful.
0
 

Expert Comment

by:shapeless
Comment Utility
Lisa,

Let me briefly describe what I understand what your problem is:

================================== Sheet "MOI":
   Col_A
:
19
20  [A20]  <=== Value to compare

================================== Sheet "Data":
   Col_D   Col_E   Col_DI   Col_DN
:
19
20        
                   DI482~512 DN482~512
================================== Sheet 3:
   Col_D   Col_E  
:
19
20         [E20]= IF((Data!$DI482:$DI512)=MOI!$A20,AVERAGE(Data!DN482:DN512)).

================================== Chart:

Well I am not sure what exactly you want to plot here. Is it [E20] ??

Is it possible that you put "zero" to all the cells in Data!DN482:DN512, so, the AVERAGE() will return "zero" (but not ERROR) BEFORE they are updated?



0
 

Expert Comment

by:shapeless
Comment Utility
In Calacuccia's first reply:

"The only problem with this is, that the chart once created forgets its link to a named (dynamic) range, and copies the corresponding range to its Data Source box."

This problem might be overcome. Instead of defining the entire data range:

MyData=OFFSET(Sheet1!$A$1,0,0,COUNTA('Sheet1!$B:$B),3)

you can define:

MyDataX=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B:$B),1) and
MyDataY=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),2)

to define X and Y seperately. NOTE: you might want to check if the above X and Y data ranges are what you need.

Then after you have a plot (you can plot any data to start with; just to give you a chart to work on), click on the curve, and on the editbox on top of the chart, you will see something like :

=SERIES(,'Data Sheet'!R1C1:R428C1,'Data Sheet'!R1C2:R428C2,1)

Just change it to:

=SERIES(,'Data Sheet'!MyDataX,'Data Sheet'!MyDataY,1)

This way, the chart will show the "non-empty cells" dynamically.




0
 

Author Comment

by:lisaswu
Comment Utility
Hi Shapeless,

Thanks for the help. But I don't know how to work with it. Let me try to make my problem clear, so
you can have better idea.
Say, I have data listed from Jan/00 to Dec/05 at Col_ A,  and I have average booking rate listed at
Col_B to Col_D . So far, I have data entered till Feb/00. The table will look like this
=============================
Col_A     Col_B     Col_C     Col_D
J/00        56%         36%       21%
F/00        98%        87%       65%
M/00      formula     formula    formula
A/00      Formula     formula   formula

===============================
Since I need the monthly average rate, so I need to enter formula, it's something like Average(F1:F30).
Now, if I highlight the whole table and plot, the total 60 months will show at the axis x, and automatic
plot does not recognize the formula, so the curve will lie on zero. With your solution, how to select the
row not the column.  I need to emphasize this: if the cells are simply empty, Calacuccia's method works
perfect.  If the cells contain FORMULA, then the warning :"Reference is invalid' will show up when I put
"MyData" in the 'Data Range' box to plot the chart.
 
0
 

Author Comment

by:lisaswu
Comment Utility
Hi Shapeless,

Thanks for the help. But I don't know how to work with it. Let me try to make my problem clear, so
you can have better idea.
Say, I have data listed from Jan/00 to Dec/05 at Col_ A,  and I have average booking rate listed at
Col_B to Col_D . So far, I have data entered till Feb/00. The table will look like this
=============================
Col_A     Col_B     Col_C     Col_D
J/00        56%         36%       21%
F/00        98%        87%       65%
M/00      formula     formula    formula
A/00      Formula     formula   formula

===============================
Since I need the monthly average rate, so I need to enter formula, it's something like Average(F1:F30).
Now, if I highlight the whole table and plot, the total 60 months will show at the axis x, and automatic
plot does not recognize the formula, so the curve will lie on zero. With your solution, how to select the
row not the column.  I need to emphasize this: if the cells are simply empty, Calacuccia's method works
perfect.  If the cells contain FORMULA, then the warning :"Reference is invalid' will show up when I put
"MyData" in the 'Data Range' box to plot the chart.
 
0
 

Expert Comment

by:shapeless
Comment Utility
I am still not exactly sure what the situation is. Question:

=============================
 Col_A     Col_B     Col_C     Col_D
1 J/00        56%         36%       21%
2 F/00        98%        87%       65%
3 M/00      formula     formula    formula
4 A/00      Formula     formula   formula
=============================

1) What exactly is the formula here?

2) In row 1 & 2 you have percentages. Did you ENTER those numbers, or they came from a formula (like row 3~4) too ??

3) You mentioned you wanna plot the entire table (with 60 months as X). Since some of the months are still "not updated", or "no value entered", you might have the left hand side of the entire X axis having curve, and right hand side no curve. If the "updated months" is not too many, like 2 months, then you will show a figure with most of the X axis (58 months) with no value. That means readers will stare at a plot with only very short curve packing at the very left side of the figure, while most of the other part just space. Is that what you want? Or you just want to plot those months with data already updated ?

The problem might not be difficult to solve if you describe the trouble as clearly as possible.
0
 
LVL 17

Accepted Solution

by:
calacuccia earned 200 total points
Comment Utility
Hi Lisaswu,

The formula for the dynamic range should do the job. The message you are getting is maybe due to other reasons. Another formula, explicitely counting the error messages in your range would look like this:

=OFFSET(Sheet1!$A$1,0,0,COUNT(IF(ISERROR(Sheet1!$B$2:$B$37)=FALSE,Sheet1!$B$2:$B$37))+1,3)

Anyway, as I said, as long as the dynamic range is correctly calculated, the chart wizard should do the job, and not return the "Reference is invalid" error message. So the first thing to do is to check the named range created (MyData).

To look at what range MyData represents, in Excel, go to Edit/GoTo, and type "MyData" (without quotation marks) in the 'Reference' box and press 'OK' button. If that does not work correcly, you have a problem in the definition of your named range.

Make sure you don't insert several named ranges with the same name, it will only create trouble. If you want to modify the 'Refers to' formula to a named range, do this via Insert/Name, and select the name 'MyData' and edit or enter the new formula directly in the 'Refers To' box.

Ensure the named range is defined in the same workbook.

Hope to help

Calacuccia

PS Tested both methods (Countif ">0" and the last formula) on a sheet with formula's returning #DIV/0! and it worked perfectly !
0
 

Author Comment

by:lisaswu
Comment Utility
Thanks a lot, Calacuccia.  You are my lifesaver. I finally figure out what cause the problem:
I put header on the month/ year column like this

Mo-Yr     t1        t2        
J-00       12%    32%
F-00
m-00

After I delete Mo-Yr and follow your instruction (countif>0), it works.  I am so happy.
0
 
LVL 17

Expert Comment

by:calacuccia
Comment Utility
If you are happy, than I am. As you see, sometimes the trouble lies in a little corner (in your case, in the top left corner) :-)

Calacuccia
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now