1

It often occurs that I write a function in one cell and then copy it into many cells. The copies then refer to their own correct rows. But if I change the function in the first cell the copies are unaffected. I have to replace all the copies with new ones. It is copy-paste programming when what I really want is to write a function in one place and call that function as though it were a copy from a bunch of subordinate cells.

Is there a technique to achieve this, or am I hoping for too much from the spreadsheet programming model?

2
  • Do the functions change, as in, refer to different cells, or are most the formula the same? If most of it is the same, you may be able to write that part into a different cell, so the cells you have to update contain a reference to that cell + the cell with the formula. Otherwise, you'll have to use macros to update the cells, which is a lot harder.
    – LPChip
    Apr 27, 2015 at 22:20
  • The functions are such as this: =IF(MOD(A2,30)=19,Strings.$A$1,"") , followed by copies referring to A5, A8, and so on. But if I wanted to change them all to say (target % 27 == 14) I'd have to make the change once and copy it to all the other similar cells. The answer below is on the right track, but has its own problems (have to learn LibreOffice BASIC, and the functions aren't attached to the spreadsheet.)
    – polymath69
    Jun 14, 2015 at 22:39

3 Answers 3

3

You may write your own function using StarBasic/LibreOffice Basic or Python. This is very easy and allows for updating a function which affects all cells where the formula is used.

As described here (courtesy Louic's weblog), just do the following:

  1. Go to Tools -> Macros -> Organise Macros -> Libreoffice Basic;
  2. Select Module1;
  3. Click edit;
  4. Enter the function code, for example:
Function Area(width, height)
    Area = width * height
End Function

Now, you can use =AREA(arg; arg2) in your spreadsheet. If you change the way the result is calculated, this affects all occurences of =AREA().

EDIT

If you don't want to put the macro into the central macro repository of your LO installation, you can save it in your ods file instead. So, it's accessible on a different PC, too. To do so, just select your current file as place to save the macro when creating it (step 1 above):

enter image description here

4
  • 1
    There is a problem with this answer; namely the function so defined does not appear to be attached to my spreadsheet. That is, I save the spreadsheet and open it on another computer and the functions aren't there. What do I need to do differently?
    – polymath69
    Jun 14, 2015 at 22:36
  • I've edited my answer to cover this.
    – tohuwawohu
    Jun 15, 2015 at 6:01
  • Is there a way to define custom functions for my computer, but also have them automatically saved into any spreadsheet in which they are used?
    – endolith
    Sep 19, 2018 at 13:45
  • @endolith: this would be worth a separate question (but i didn't check if this wasn't asked before anywhere else)...
    – tohuwawohu
    Sep 19, 2018 at 13:48
1

A simple solution without Macros: just put the variable arguments into separate cells. So instead of

=IF(MOD(A2,30)=19,Strings.$A$1,"")

(with the values 30 and 19 fixed), just replace this by

=IF(MOD(A2,$E$1)=$E$2,Strings.$A$1,"")

and put the values 30 and 19 into cells E1 and E2, respectively. Now, if you change E1 and/or E2, every formula will use that values.

If there are certain combinations of those two values you need to apply often, you may define scenarios accordingly

0

You can create a comparison function cell and refer to that one from your other functions.

Say you create this function in A10, then A10 would look like this:

=if(MOD(A2,30)=19,1,0)

Now, if you do your other function in say A11, normally your function looks like this: =IF(MOD(A2,30)=19,Strings.$A$1,"")

Now it will look like this: =if(A10=1,Strings.$A$1,"")

If you need to change the comparison part, you change A10 and it updates for all cells that refer to it.

2
  • I don't understand this answer. You refer to A2 in too many places... as the operand of the function and as the location of the expression. Could you disambiguate, to say, A1 has a data item, A2 has the generic function, and A3 uses the generic function to refer to A1 and $A$2. Then B3 could use the generic to refer to B1 (data) and $A$2. Thank you.
    – polymath69
    Jun 17, 2015 at 20:27
  • @polymath69 ah, you're correct. I'll edit the answer to reflect this. I just copied the functiosn from the OP and completely forgot that it would turn into a nested loop.
    – LPChip
    Jun 17, 2015 at 20:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .