Re: [libreoffice-users] How to paste a function so Calc doesn't change addresses in it?

2011/12/26 Csányi Pál <csanyipal@gmail.com>

> 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
>
> --
> For unsubscribe instructions e-mail to: users+help@global.libreoffice.**
> org <users%2Bhelp@global.libreoffice.org>
> Problems? http://www.libreoffice.org/**get-help/mailing-lists/how-to-**
> unsubscribe/<http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/>
> Posting guidelines + more: http://wiki.**documentfoundation.org/**
> Netiquette <http://wiki.documentfoundation.org/Netiquette>
> List archive: 
> http://listarchives.**libreoffice.org/global/users/<http://listarchives.libreoffice.org/global/users/>
> All messages sent to this list will be publicly archived and cannot be
> deleted
>
> Two ways I know of...

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.

-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted