How to paste a function so Calc doesn't change addresses in it?

users
#1

Hi,

I'm working on a spreadsheet and I want to copy/paste a function.

When I copy/paste the function Calc increase some address in the functions that I have in the cell and those addresses aren't proper for my purpose.

Eg. I want to copy/paste the value of a cell:
=HA(DARABÜRES('1.'.AD6);"0";HA('1.'.AD6="1";"-";HA("1"<'1.'.AD6<"6";"+";"hamis")))

but when I paste it, I get:
=HA(DARABÜRES('1.'.AD7);"0";HA('1.'.AD7="1";"-";HA("1"<'1.'.AD7<"6";"+";"hamis")))

and that isn't good for me, because I want to get
=HA(DARABÜRES('1.'.AD6);"0";HA('1.'.AD6="1";"-";HA("1"<'1.'.AD6<"6";"+";"hamis")))

in the new cell;
actually I would like to get:
=HA(DARABÜRES('2.'.AD6);"0";HA('2.'.AD6="1";"-";HA("1"<'2.'.AD6<"6";"+";"hamis")))

but I think that that this can't be achieve with Calc, right?

How can I copy/paste the function so so the addresses in the function remain the same as in the original cell?

Best Regards, Pál

#2

Hi,

Message du 26/12/11 16:25
De : "Csányi Pál"
A : "Libre Office"
Copie à :
Objet : [libreoffice-users] How to paste a function so Calc doesn't change addresses in it?

Hi,

I'm working on a spreadsheet and I want to copy/paste a function.

When I copy/paste the function Calc increase some address in the
functions that I have in the cell and those addresses aren't proper for
my purpose.

Eg. I want to copy/paste the value of a cell:
=HA(DARABÜRES('1.'.AD6);"0";HA('1.'.AD6="1";"-";HA("1"<'1.'.AD6<"6";"+";"hamis")))

but when I paste it, I get:
=HA(DARABÜRES('1.'.AD7);"0";HA('1.'.AD7="1";"-";HA("1"<'1.'.AD7<"6";"+";"hamis")))

and that isn't good for me, because I want to get
=HA(DARABÜRES('1.'.AD6);"0";HA('1.'.AD6="1";"-";HA("1"<'1.'.AD6<"6";"+";"hamis")))

in the new cell;
actually I would like to get:
=HA(DARABÜRES('2.'.AD6);"0";HA('2.'.AD6="1";"-";HA("1"<'2.'.AD6<"6";"+";"hamis")))

but I think that that this can't be achieve with Calc, right?

How can I copy/paste the function so so the addresses in the function
remain the same as in the original cell?

Best Regards, Pál

Use "absolute cell reference".
Press Shift+F4 will change relative to absolute reference for the active cell.

Your formula =HA(DARABÜRES('1.'.AD6);"0";HA('1.'.AD6="1";"-";HA("1"<'1.'.AD6<"6";"+";"hamis")))
will change in =HA(DARABÜRES($'1.'.$AD$6);"0";HA($'1.'.$AD$6="1";"-";HA("1"<$'1.'.$AD$6<"6";"+";"hamis")))

$ before sheet, column or row reference fixed them. They do not increase when copied.

Gérard

#3

1) use a "$" as the part of the address you don't want to change when
copy/pasting. So
=HA(DARABÜRES('1.'.AD6);"0";HA('1.'.AD6="1";"-";HA("1"<'1.'.AD6<"6";"+";"hamis")))

becomes
=HA(DARABÜRES('1.'.$AD$6);"0";HA('1.'.$AD$6="1";"-";HA("1"<'1.'.$AD$6<"6";"+";"hamis")))

I believe there is a function key that can be used to cycle through
absolute cell addresses when creating a formula, e.g. cycles through AD6,
$AD6, AD$6, $AD$6 as addresses when editing the formula. That avoids the
need to type in the "$"

2) name the cells you wish to stay the same as you copy the function from
cell to cell and use the cell names in the formula.

#4

Hi Gérard,

thank you very much for your help!

#5

Hi Alan,

thank you very much for your help!

#6

...one other thing using the "$" works for the row reference or the column
reference so two "$" are needed, one next to the column value one next to
the row value, to keep both from changing. If you want the column reference
to remain constant but allow the row to change only use "$" in front of the
column letter. If you want the row to remain constant but allow the column
to change only use the "$" in front of the row reference.

constant column when formula is copied $B6. A formula containing this
reference will always us "B" as the column but the row will change if the
formula is copied to a different row.

constant row when formula is copied B$6. A formula containing this
reference will always us "6" as the row but the column will change if the
formula is copied to a different column.

#7

Alan Boba <aboba0@gmail.com> writes:

constant column when formula is copied $B6. A formula containing this
reference will always us "B" as the column but the row will change if the
formula is copied to a different row.

It works for the next raw, but how can I use this behavior when I want to
copy to a different column?

When I'm trying to copy to the next column:
=HA(DARABÜRES('5.'.$AD6);"0";HA('5.'.$AD6="1";"-";HA("1"<'5.'.$AD6<"6";"+";"hamis")))

It remains like this:
=HA(DARABÜRES('5.'.$AD6);"0";HA('5.'.$AD6="1";"-";HA("1"<'5.'.$AD6<"6";"+";"hamis")))

and the address $AD6 doesn't changed to $AD7, why?

#8

I'm a bit lost by your question. Copying the first formula to another cell
_on_the_same_row_ should have exactly the result you've shown.

If the cell reference in the formula should change from $AD6 to $AD7 then
after copying the formula it must be pasted one row below the row it is
copied from. If the formula is in cell A1 and copied from there to cell B2
the cell reference in the formula will be updated from $AD6 to $AD7. Note
the destination I've given is on a different row and in a different column.
However only the row number would change because the column value is
preceded by "$". If the formula is pasted into a cell two rows lower then
the cell reference would change from $AD6 to $AD8 and so on.

#9

Hi,

How can I copy/paste the function so so the addresses in the
function remain the same as in the original cell?

The answer is easy and it is here:
http://help.libreoffice.org/Calc/Addresses_and_References,_Absolute_and_Relative

Stefan
:slight_smile:

#10

Alan Boba <aboba0@gmail.com> writes:

Alan Boba <aboba0@gmail.com> writes:

It works for the next raw, but how can I use this behavior when I want to
copy to a different column?

When I'm trying to copy to the next column:

=HA(DARABÜRES('5.'.$AD6);"0";HA('5.'.$AD6="1";"-";HA("1"<'5.'.$AD6<"6";"+";"hamis")))

It remains like this:

=HA(DARABÜRES('5.'.$AD6);"0";HA('5.'.$AD6="1";"-";HA("1"<'5.'.$AD6<"6";"+";"hamis")))

and the address $AD6 doesn't changed to $AD7, why?

I'm a bit lost by your question. Copying the first formula to another cell
_on_the_same_row_ should have exactly the result you've shown.

My question abowe is about that that if one want to copy a formula into
a cell say one raw lower, then addresses in formula would change without
'$' prefix but wouldn't change (again, without '$' prefix) if the same
formula want to copy into a cell that is in the same raw. I hope I'm
clear now. (Sorry for my English.)

If the cell reference in the formula should change from $AD6 to $AD7 then
after copying the formula it must be pasted one row below the row it is
copied from. If the formula is in cell A1 and copied from there to cell B2
the cell reference in the formula will be updated from $AD6 to $AD7. Note
the destination I've given is on a different row and in a different column.
However only the row number would change because the column value is
preceded by "$". If the formula is pasted into a cell two rows lower then
the cell reference would change from $AD6 to $AD8 and so on.

All right, I understand You perfectly. Thanks for the explanation!

#11

Alan Boba <aboba0@gmail.com> writes:

>
>> Alan Boba <aboba0@gmail.com> writes:
>>
>>

>> It works for the next raw, but how can I use this behavior when I want

to

>> copy to a different column?
>>
>> When I'm trying to copy to the next column:
>>
>>

=HA(DARABÜRES('5.'.$AD6);"0";HA('5.'.$AD6="1";"-";HA("1"<'5.'.$AD6<"6";"+";"hamis")))

>>
>> It remains like this:
>>
>>

=HA(DARABÜRES('5.'.$AD6);"0";HA('5.'.$AD6="1";"-";HA("1"<'5.'.$AD6<"6";"+";"hamis")))

>>
>> and the address $AD6 doesn't changed to $AD7, why?
>>
>
> I'm a bit lost by your question. Copying the first formula to another

cell

> _on_the_same_row_ should have exactly the result you've shown.

My question abowe is about that that if one want to copy a formula into
a cell say one raw lower, then addresses in formula would change without
'$' prefix but wouldn't change (again, without '$' prefix) if the same
formula want to copy into a cell that is in the same raw. I hope I'm
clear now. (Sorry for my English.)

> If the cell reference in the formula should change from $AD6 to $AD7

then

> after copying the formula it must be pasted one row below the row it is
> copied from. If the formula is in cell A1 and copied from there to cell

B2

> the cell reference in the formula will be updated from $AD6 to $AD7.

Note

> the destination I've given is on a different row and in a different

column.

> However only the row number would change because the column value is
> preceded by "$". If the formula is pasted into a cell two rows lower

then

> the cell reference would change from $AD6 to $AD8 and so on.

All right, I understand You perfectly. Thanks for the explanation!

--
Regards, Pal

You're welcome. Glad I was able to help.

#12

Alan Boba<aboba0@gmail.com> writes:

Alan Boba<aboba0@gmail.com> writes:

It works for the next raw, but how can I use this behavior when I want to
copy to a different column?

When I'm trying to copy to the next column:

=HA(DARABÜRES('5.'.$AD6);"0";HA('5.'.$AD6="1";"-";HA("1"<'5.'.$AD6<"6";"+";"hamis")))

It remains like this:

=HA(DARABÜRES('5.'.$AD6);"0";HA('5.'.$AD6="1";"-";HA("1"<'5.'.$AD6<"6";"+";"hamis")))

and the address $AD6 doesn't changed to $AD7, why?

I'm a bit lost by your question. Copying the first formula to another cell
_on_the_same_row_ should have exactly the result you've shown.

My question abowe is about that that if one want to copy a formula into
a cell say one raw lower, then addresses in formula would change without
'$' prefix but wouldn't change (again, without '$' prefix) if the same
formula want to copy into a cell that is in the same raw. I hope I'm
clear now. (Sorry for my English.)

Your English is fine. For example if you had a formula in C7 with references to A7 and B7, =A7+B7 copying the formula to G9 should result in =E9+F9, the copy paste references to the cells using relative location.

If the formula was originally =$A$7+$B$7 pasting to any other cell will not change the references, it is absolute and will always refer to A7 and B7. Pasting into K7, H4, or C34 will always use the values in A7 and B7.

If the formula was originally =$A7+$B7 pasting into K7 has no change because the row is the same. Pasting into H4 results in =$A4+$B4 and C34 results in =$A34+$B34