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

[diegowar]

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

[mgroenescheij]

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]

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]

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

[diegowar]

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]

[diegowar]

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