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 2 conditionally auto-re-generate worksheet content?

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


Joined: 16 Jun 2007
Posts: 2

PostPosted: Sat Jun 16, 2007 3:29 pm    Post subject: how 2 conditionally auto-re-generate worksheet content? Reply with quote

hi,

i'm attempting my first script-driven dynamic generation & manipulation of data.

i'd like to create a script to conditionally populate a 2nd worksheet in a workbook, given a value entered into a cell in a first worksheet.

in principle, the idea is simple ... but i'm simply lost as to where/how to undertake this.

e.g.,

on sheet1, enter #N into a cell.

on change of #N,

in sheet#2 automatically,

(1) clear DATA from ALL rows > #X, in columns "B" & "C"
(2) for row_count of i=1->#N,

populate cell @ (row= #X+i , col="B") with "i"
populate cell @ (row=#X+i, col"B") with a formula/calculation based on the value of "i", i.e., f(i)

then, on sheet1, populate Chart data into a defined area, with X- & Y-data defined by the auto-created Sheet2 data.

i'd appreciate any hand-holding/introduction on how to do this.

thank you!

rob
Back to top
View user's profile Send private message
squenson
Super User
Super User


Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

PostPosted: Sat Jun 16, 2007 10:16 pm    Post subject: Reply with quote

Rob,

If #N is in cell Sheet1.A1, then you should put in Sheet2:
Code:
Cell A1: =IF(ROW()>Sheet1.$A$1;" ";ROW())
Cell B1: =IF(ROW()>Sheet1.$A$1;"";function1(A1))
Cell C1: =IF(ROW()>Sheet1.$A$1;"";function2(A1))

Then copy the cell A1:C1 down Sheet2
If your chart was created using the full possible range of rows in Sheet2, it should automatically update when you change the value of #N
_________________
Help us to help you: Add [Solved] to the title of the thread if you agree with the answer
>>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
Back to top
View user's profile Send private message
rb.forums
Newbie
Newbie


Joined: 16 Jun 2007
Posts: 2

PostPosted: Mon Jun 18, 2007 7:23 am    Post subject: Reply with quote

Hi,

squenson wrote:

If #N is in cell Sheet1.A1, then you should put in Sheet2:
Code:
Cell A1: =IF(ROW()>Sheet1.$A$1;" ";ROW())
Cell B1: =IF(ROW()>Sheet1.$A$1;"";function1(A1))
Cell C1: =IF(ROW()>Sheet1.$A$1;"";function2(A1))

Then copy the cell A1:C1 down Sheet2
If your chart was created using the full possible range of rows in Sheet2, it should automatically update when you change the value of #N


That's neat. Thanks!

Iiuc, your suggestion, though, still requires "copying down" the formulas for at least num_rows >= #N.

Is it possible to have the -- or probably more correctly, a NEW -- sheet AUTO generated for any given #N?

Thanks again!

Rob
Back to top
View user's profile Send private message
squenson
Super User
Super User


Joined: 09 Mar 2007
Posts: 690
Location: Nis, Serbia

PostPosted: Mon Jun 18, 2007 9:53 am    Post subject: Reply with quote

Rob,

The only way to generate cell content ex nihilo (from nothing) is to use a macro. In your case, either you create a button and you click on it each time you change the value of N, or you create an event that monitors the change in that cell, and runs the same code as the button. The code would consist of filling a range of length N with the formulas.

I have posted something about events in the past months and I reused it yesterday for someone else: http://www.oooforum.org/forum/viewtopic.phtml?t=54726&highlight=
_________________
Help us to help you: Add [Solved] to the title of the thread if you agree with the answer
>>> Do you know the new OOo support forum http://user.services.openoffice.org/en/forum/index.php? <<<
Back to top
View user's profile Send private message
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