# I need help in copying cells with formulas in calc

Anonymous

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.

delete close retag edit

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?

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

Sort by » oldest newest most voted

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


## Donate

LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!