Excel graphs and auto macros

Excel graphs and auto macros

Didn't find your answer?

Here's the story. I have a great tool that drops figures from my General Ledger into Excel for me and will zoom the report off to the manager by e-mail at the same time. PeopleSoft is great - hands off!

Anyway. I have a report that has the total actual and budgeted expenditure for each month. Actuals are one row, Budgets are the other row and each month is a separate column.

Now the manager is good at pictures, but frankly lousy with numbers so I thought I'd produce him a graph showing the cumulative budget and actuals for each month. This was easy, BUT...

... but when I run the graph the Actuals for future periods (which are 0 obviously) show as zero and make my graph look silly. SO I need a way to stop these items showing. My 2 as-yet-unsuccessful ways of tackling this are:

1) Change the source of the Actuals line to pick up a range that changes with each month. This fails as I cannot include a formula within the series range and if I ask the series to pick up a range elsewhere it gets confused and tries to plot to the physical location of my formula.

2) Use a macro to clear 0 values in cells where period is greater than the current period. I can get this macro to work, but I need it to run when the file is opened.

So how do you do it? How do you get a graph to run on a conditional range or how can you clear cells based on a formula elsewhere?
Chris Fyfe

Replies (3)

Please login or register to join the discussion.

avatar
By CP Fyfe
17th Jun 2003 14:33

Super
Wow thanks

I was about to give up and use Bar Charts which look clumsy, but don't have the same problem.

Cheers


Chris

Thanks (0)
avatar
By AnonymousUser
26th Jun 2003 11:10

Your option 2)
Maybe I've misunderstood that you have a problem with "I need it to run when the file is opened". You could run a Workbook_Open macro that runs automatically when the file opens. Go into the VBA editor and select the workbook in the VB project window, double click it and then add something like this:

Private Sub Workbook_Open()
Your_Existing_Macro's_Name
End Sub

John

Thanks (0)
avatar
By AnonymousUser
08th Jan 2008 07:27

please mail me the file
Hi,

I am facing a similar problem with Excel charts.
I have a file with cell references & few values are blank but when the chart is created it looks silly.

can you please mail me the file with macros.

Bhagwathi

Thanks (0)