Re: [libreoffice-users] Calc - avoiding circular references

At 13:25 23/10/2013 +1000, Dave Kimble wrote:
> Ubuntu 13.04 > LibreOffice > Calc 4.0.2.2
> I'm a veteran Excel-er, but very new to Calc.  I've had to invent 
> some syntax in the following question - hope you can follow it.
>
> In Excel, to set up a row of column headings (years say), you would do:
> A1 => 1965 [Enter]
> B1:AX1 => formula(=A1+1) [Control+Enter]
>
> and B1 would become set to 1966, ... , AX1 would become set to 2013.
> What is the equivalent syntax in Calc?

I don't know of one.

> If I try:
> A1 => 1965 [Enter]
> B1:AX1 => array formula(=(A1:AW1)+1) [Control+Shift+Enter]
>
> B1 is calculated correctly, but the other cells get Err:522 - 
> circular reference, which I agree it is in a way, but in Excel this 
> doesn't matter as the looping through the list is done left to 
> right, so it isn't really circular.

This is different, as you are creating an array, not a list of 
separate, interdependent formulae - though that apparently works in Excel too.

Possible workarounds:

o In A1, enter 1965.  Drag the fill handle along to AX1 (which I make 
2014, by the way).

o In A1, enter 1965.  In B1, enter =A1+1.  Copy B1 and paste into 
C1:AX1 (or into B1:AX1 if easier).

o In A1:AX1, enter =1964+COLUMN() [Ctrl+Shift+Enter].

o Select row A.  Go to Edit | Fill > | Series... . Choose Right for 
Direction and Linear for Series type.  Enter 1965 for Start value, 
2014 for End value, and 1 for Increment.  OK.  (No need to know in 
advance where the list will end.)

Note that one of these processes leaves you with separate formulae, 
one with an array, and two with separate values.  This may make a 
difference if you later need to change anything.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@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