View previous topic :: View next topic 
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$56,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 

Back to top 


mgroenescheij Super User
Joined: 20 Apr 2011 Posts: 870 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). 

Back to top 


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 ^^ 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 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 _________________ 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 


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 

Back to top 


ken johnson Super User
Joined: 23 Apr 2009 Posts: 2032 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 _________________ If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button). 

Back to top 


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 

Back to top 




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
