[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
diegowar
Newbie

Joined: 20 Jan 2012
Posts: 4
Location: France

 Posted: Fri Jan 20, 2012 5:14 pm    Post subject: [Solved] How to copy a formula from a different file ? 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 Last edited by diegowar on Sat Jan 21, 2012 11:03 am; edited 1 time in total
mgroenescheij
Super User

Joined: 20 Apr 2011
Posts: 862
Location: Australia

 Posted: Fri Jan 20, 2012 5:33 pm    Post subject: 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).
diegowar
Newbie

Joined: 20 Jan 2012
Posts: 4
Location: France

 Posted: Fri Jan 20, 2012 6:57 pm    Post subject: 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 ^^
ken johnson
Super User

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

Posted: Sat Jan 21, 2012 7:56 am    Post subject:

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
_________________

Last edited by ken johnson on Thu Sep 27, 2012 2:53 am; edited 2 times in total
diegowar
Newbie

Joined: 20 Jan 2012
Posts: 4
Location: France

 Posted: Sat Jan 21, 2012 11:02 am    Post subject: 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
ken johnson
Super User

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

Posted: Sat Jan 21, 2012 7:56 pm    Post subject:

 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
_________________
diegowar
Newbie

Joined: 20 Jan 2012
Posts: 4
Location: France

 Posted: Sat Jan 21, 2012 9:58 pm    Post subject: 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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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