2013 Archives by date, by thread · List index

# 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
```