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

[Solved] How to copy a formula from a different file ?

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


Joined: 20 Jan 2012
Posts: 4
Location: France

PostPosted: Fri Jan 20, 2012 5:14 pm    Post subject: [Solved] How to copy a formula from a different file ? Reply with quote

Hi,

What I'm trying to do is this :

In various files, I've data arranged in the same way, and in each of these files I'd like to calculate something depending on the data of the file.

I've got a formula, so I just have to copy the formula in each file and all will work.

Well, it does, but the thing is, since I've done this, I've had to change twice of formula, and that means that twice I had to manually change the formula in all 300 files, so tedious.

What I would like is to save the formula in a separate file, so that I only have to change it there, and each time I open one of the files, it'll check if the formula has changed and if it has, apply the new one.


If that's any help, the formula is :

=(-4,7*$G$3+2*(SUMPRODUCT(INDIRECT("$C$4:$C"&$G$2+4)))-0,4*(SUMPRODUCT(INDIRECT("$C$4:$C"&$G$2+4);INDIRECT("$C$4:$C"&$G$2+4)))+0,035*(SUMPRODUCT(INDIRECT("$C$4:$C"&$G$2+4);INDIRECT("$C$4:$C"&$G$2+4);INDIRECT("$C$4:$C"&$G$2+4)))+($G$3*$G$5*$G$5*$G$5*$G$5*$G$5)/5000)*IF($G$5>6,5;1+($G$5-6,5)*IF($G$2>10;IF($G$3>10;1;$G$3/10);$G$3/$G$2);1)

so as you can see, all the data I'm using is in the same file where the formula is to be calculated.

Any help would be much appreciated Smile


Last edited by diegowar on Sat Jan 21, 2012 11:03 am; edited 1 time in total
Back to top
View user's profile Send private message
mgroenescheij
Super User
Super User


Joined: 20 Apr 2011
Posts: 870
Location: Australia

PostPosted: Fri Jan 20, 2012 5:33 pm    Post subject: Reply with quote

Hi,

I have no idea how to achieve it, but seeing that you have 300 files with the same structure of data I think you should start thinking about to transfer it to a database.

Martin
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
diegowar
Newbie
Newbie


Joined: 20 Jan 2012
Posts: 4
Location: France

PostPosted: Fri Jan 20, 2012 6:57 pm    Post subject: Reply with quote

Hehe, you can't know how angry I am I didn't started with a database, it'd have make everything better, but at the time I started I didn't know much about databases (today I've studied them in good length). Transfering everything to a database can be done, but it'd take... I don't even want to think about it, I know it's a matter of at least 6 hours ^^
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat Jan 21, 2012 7:56 am    Post subject: Reply with quote

Your formula needs to be changed so that every cell reference includes a reference to the relevant spreadsheet document.
If all of your spreadsheet documents with your columns C and G data are in the same folder then you can have one spreadsheet document with the formula returning the calculated result for any particular spreadsheet document.
With this attached doc (Multifile Formula.ods)...
http://www.mediafire.com/view/?ep9x2o8m5c9h7x7
There is data in column C starting at C4 and data in G2, G3 and G5.
The formula in A1, =CELL("filename"), returns the document's filename.
All other documents in the same folder have exactly the same filename up to the last "/".
The formula in A2, =LEFT(A1;LEN(MID(A1;FIND(CHAR(1);SUBSTITUTE(A1;"/";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))));LEN(A1)))+1), removes all the filename characters after the last "/".
A3 is where you can enter the name of a spreadsheet document whose C and G data you want the formula to use.
The formula in A4, =$A$2&$A3&".ods'#$Sheet1.", returns a string that is used by the INDIRECT functions in a new version of your formula. Note that I have assumed each of your spreadsheet documents have their data on Sheet1. If this is note the case then the A4 formula will need to be adjusted.
The new version of your formula is in F8...
Code:
=(-4.7*INDIRECT(A4&"$G$3")+2*(SUMPRODUCT(INDIRECT(A4&"$C$4:$C"&INDIRECT(A4&"$G$2")+4)))-0.4*(SUMPRODUCT(INDIRECT(A4&"$C$4:$C"&INDIRECT(A4&"$G$2")+4)^2))+35*(SUMPRODUCT(INDIRECT(A4&"$C$4:$C"&INDIRECT(A4&"$G$2")+4)^3))+INDIRECT(A4&"$G$3")*INDIRECT(A4&"$G$5")^5/5000)*IF(INDIRECT(A4&"$G$5")>6.5;1+(INDIRECT(A4&"$G$5")-6.5)*IF(INDIRECT(A4&"$G$2")>10;IF(INDIRECT(A4&"$G$3")>10;1;INDIRECT(A4&"$G$3")/10);INDIRECT(A4&"$G$3")/INDIRECT(A4&"$G$2"));1)

The value returned by this formula depends on the document name you enter into A3.
Your original formula is in E8. It always uses the column C and G data on this sheet in this document.
EDIT: You can ignore or even delete the stuff in E12:F19 where your formula has been broken down into smaller distinguishable parts.
EDIT2:Link updated after changing formula in A2.
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).


Last edited by ken johnson on Thu Sep 27, 2012 2:53 am; edited 2 times in total
Back to top
View user's profile Send private message
diegowar
Newbie
Newbie


Joined: 20 Jan 2012
Posts: 4
Location: France

PostPosted: Sat Jan 21, 2012 11:02 am    Post subject: Reply with quote

Aaaaaah I almost went mad with your solution ^^


In fact, it works great.


The thing is I tried to make it work for hours, not understanding why there were errors everywhere, when I decided to upgrade my openoffice from version 3.1 to version 3.3, and that solved everything that wasn't working.


Oh, and one small thing, shouldn't A2 be :

=LEFT(A1;FIND(CHAR(1);SUBSTITUTE(A1;"/";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"/";"")))))

instead of :

=LEFT(A1;LEN(MID(A1;FIND(CHAR(1);SUBSTITUTE(A1;"/";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))));LEN(A1)))+1)

?

Because the FIND function with these parameters, returns the number of char up to the last '/', so that's what we want to give to "LEFT", isn't it ? Actually I'm not sure what the MID function does here... and if I change the file name with the current A2 formula, it changes the A2 value, which I don't think should happen.


Anyway, thanks for the help Smile
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat Jan 21, 2012 7:56 pm    Post subject: Reply with quote

diegowar wrote:
The thing is I tried to make it work for hours, not understanding why there were errors everywhere, when I decided to upgrade my openoffice from version 3.1 to version 3.3, and that solved everything that wasn't working.
Probably something to do with improvement in the INDIRECT function.


diegowar wrote:
Oh, and one small thing, shouldn't A2 be :

=LEFT(A1;FIND(CHAR(1);SUBSTITUTE(A1;"/";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"/";"")))))

instead of :

=LEFT(A1;LEN(MID(A1;FIND(CHAR(1);SUBSTITUTE(A1;"/";CHAR(1);LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))));LEN(A1)))+1)

?

Because the FIND function with these parameters, returns the number of char up to the last '/', so that's what we want to give to "LEFT", isn't it ?
Well spotted!
I originally was using the MID function because I was foolishly wanting to exclude the leading apostrophe, then in A4 I was putting the same apostrophe back in with ="'"&$A$2&$A3&".ods'#$Sheet1."
When I eventually realised the foolishness of my ways I amended the A2 formula to include the leading apostrophe. Instead of doing away with the MID function and using the LEFT function I fiddled with the MID function. Your solution is the ideal one I should have used.
I have changed the A2 formula and updated the link.

diegowar wrote:
Actually I'm not sure what the MID function does here... and if I change the file name with the current A2 formula, it changes the A2 value, which I don't think should happen.
Not sure what you mean here.
Changing the file name in A3 can't have any effect on the A2 formula and this applies to both my original A2 formula and the new one.
Whatever you enter into A3 gets concatenated with the A2 formula result and ".ods'#$Sheet1."

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
diegowar
Newbie
Newbie


Joined: 20 Jan 2012
Posts: 4
Location: France

PostPosted: Sat Jan 21, 2012 9:58 pm    Post subject: Reply with quote

I meant if I change the "real" file name (not A3, but the *.ods), it changed A2 (before my correction).

But everything's good now Smile
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