OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How do you do dynamically updating chart ranges in Calc?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
spameater
Newbie
Newbie


Joined: 09 Dec 2004
Posts: 2

PostPosted: Thu Dec 09, 2004 6:11 pm    Post subject: How do you do dynamically updating chart ranges in Calc? Reply with quote

Hi there everybody,

I was wondering how you do dynamically updating chart ranges in Calc?

Just having moved over from Excel and the rest of MS office today everything works fine on my spreadsheets and word documents (horray!) except the dynamically updating ranges!

Here's a very simple example of what I mean. I have a chart/graph that has values over months like the following:
Aug 1st $1000
Sept 1st $1500
Oct 1st $1250
Nov 1st $1750
etc.

Now each month I add new data for example something for December. I want the chart/graph that is being displayed to automatically detect there is new data and include it in the dynamic chart I have created.

Note this isn't a trivial issue for me. Every major spreadsheet I have created has dynamically updating charts, so its critical this works for me(!).

In Excel I would use a process simlar to what is described on this website to do this:
http://www.meadinkent.co.uk/xlgraphoffset.htm

Note this makes use of defined names, function OFFSET and function COUNT in EXCEL. There are equivalent functions offered in Open Office from what I understand.

How do I do this in Calc (dynamically updating charts) and can it in fact be done? Hopefully it can be done!

Thank you for your help!
Back to top
View user's profile Send private message
spameater
Newbie
Newbie


Joined: 09 Dec 2004
Posts: 2

PostPosted: Sun Dec 12, 2004 6:15 pm    Post subject: ?!? Reply with quote

So NOBODY has any ideas?

*sigh*

...

<begins uninstalling OpenOffice>
Back to top
View user's profile Send private message
davidh182
OOo Advocate
OOo Advocate


Joined: 01 Apr 2004
Posts: 413

PostPosted: Mon Dec 13, 2004 8:03 am    Post subject: Reply with quote

The following macro will search column B for extent of data and then update chart accordingly:

Code:

sub alterchart
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim oCurSheet as object
   oCurSheet =  StarDesktop.CurrentComponent.CurrentController.ActiveSheet

while oCurSheet.getCellByPosition(0,row).String<>""
   row=row+1
wend

dim args2(3) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Name"
args2(0).Value = "Object 1"
args2(1).Name = "Range"
args2(1).Value = "$Sheet1.$A$1:$B$"&row
args2(2).Name = "ColHeaders"
args2(2).Value = true
args2(3).Name = "RowHeaders"
args2(3).Value = false

dispatcher.executeDispatch(document, ".uno:ChangeChartData", "", 0, args2())


end sub


This assumes data are in columns A and B starting at row 1, and the chart is called "Object 1" - the default name for the first chart. (You can find out the name by recording an action on the chart).

Then you need to trick OO into executing the macro whenever something is added. This can be done by defining a dummy function:

Code:


function dummy(rng()) as String
alterchart

dummy=""
end function


and setting the function =dummy(A1:B200) in a spare cell
Then whenever a cell in A1:B200 is changed, the function "dummy" will be called which will call alterchart. There may be pitfalls to this approach depending on the exact behviour required...
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group