Ask Your Question

I need help in copying cells with formulas in calc [closed]

asked 2012-04-17 19:47:04 +0200

anonymous user


Sorry for my english, but i need copy some formulas in different cells in calc, but I need that they do not fit the formulas. We have more of 6000 cells to edit, and I'm doing manually.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-17 00:53:17.025877


Can you provide a short example of what you are trying to do? Can you upload a file with the formula, explaining where you want to copy it from and where you want to copy it to?

Pedro gravatar imagePedro ( 2012-04-17 20:43:32 +0200 )edit

1 Answer

Sort by » oldest newest most voted

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

hunteke gravatar image

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
edit flag offensive delete link more

Question Tools


Asked: 2012-04-17 19:47:04 +0200

Seen: 404 times

Last updated: Jul 14 '12