I need help in copying cells with formulas in calc

answered 2012-07-14 09:41:04 +0200

hunteke
175 ● 4 ● 10

As Pedro alluded, you've unfortunately not provided enough information for folks to specifically help with your problem.

However, I can try at an answer. I see three general approaches to updating a large number of formulas:

(1) If the change of formula between cells is trivial, like:

   A|  B  |     C                       A|  B  |     C
-----------------------              -----------------------
1|15|=A1*2|=SUM(A$1:A1)     --->     1|15|=A1*2|=SUM(A$1:B1)
2|16|=A2*2|=SUM(A$1:A2)              2|16|=A2*2|=SUM(A$1:B2)
3|10|=A3*2|=SUM(A$1:A3)              3|10|=A3*2|=SUM(A$1:B3)

Then all you need do is change the cell item in C1, and "fill down" to the rest. LibO will automatically update:

   A|  B  |     C                       A|  B  |     C
-----------------------              -----------------------
1|15|=A1*2|=SUM(A$1:A1)     --->     1|15|=A1*2|=SUM(A$1:B1) (fill down)
2|16|=A2*2|=SUM(A$1:A2)              2|16|=A2*2|=SUM(A$1:A2) ( from C1 )
3|10|=A3*2|=SUM(A$1:A3)              3|10|=A3*2|=SUM(A$1:A3) ( to C3 )

(2) However, if you have more complicated changes, then you may want to copy the representative formulas to a text editor, and make changes to N copies (e.g. 6,000) of it. You're still manipulating N copies, but with the text editor interface, you may have more luck with search and replace, or easier "down-arrow-backspace-digit" manipulation.

(3) If you have any programming or scripting experience, you can also make a simple Python script to write the formulas you need, which you can then copy-paste back into LibO. For example:

from sys import stdout as SO

for i in range(1, 6001):
   for j in range(1, 3):
      SO.write( '=SUM(A$1:B{}) - C{},'.format(i, j) )
   SO.write('\n') # next row