Calc Options > Libreoffice Calc > General > Expand references when new columns/rows are inserted

users
#1

Hi all,

This doesn't seem to be working for me, and hasn't for a long time, I
just keep forgetting to ask and go file a bug if it is broken.

I have a spreadsheet that has numerous formulas in certain cells in each
row.

With the option referenced in this email subject enabled, when I insert
a new row, shouldn't it automatically populate the corresponding cells
in the new row with the formulas from the row above (or below if I
inserted above)?

I also have 'Expand formatting' enabled, and all cells in the new row do
inherit the formatting of the cells in the rows above/below, but not the
formulas.

I would expect it to be the same as if I do a fill up/down from the
cell/formula below/above, which I have to manually do now since this
isn't working.

Thanks

#2

No, I think not. It should do what it claims, i.e. expand references, not duplicate formulae. It's not really about what is in the new rows or columns, but about references *elsewhere* to ranges which may or may not need to include them. If you have a reference somewhere to, say, B3:B9 and you introduce a new row 6, such references will automatically be expanded to B3:B10 so as to include the new cell B6 as well as the original seven cells. This applies irrespective of the condition of the option you identify.

If, alternatively, you introduce a new row 10 (or a new row 3) - adjacent to instead of within the existing range - references elsewhere will be updated only if the option in question is ticked.

I trust this helps.

Brian Barker

#3

postmaster@btinternet.com)
Expand references when new columns/rows are inserted
MIME-Version: 1.0
Content-Type: multipart/alternative;
   boundary="----=_Part_1_1114535632.1483574101274"
X-Mailer: MailDroid/4.49 (Android 4.4.2)
User-Agent: MailDroid/4.49 (Android 4.4.2)

------=_Part_1_1114535632.1483574101274
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: base64

SSBob3BlIHRoZXNlIGNvbW1lbnRzIGFyZSBoZWxwZnVsLgoKSW5zZXJ0aW5nIG9yIGRlbGV0aW5n
IG9uZSBvciBtb3JlIHJvd3MgKGNvbHVtbnMpIHJlcXVpcmVzIG1hbnVhbCBpbnRlcnZlbnRpb24g
aW4gdGVybXMgb2YgcGF0Y2hpbmcgcmVmZXJlbmNlcyBpbiBtYW55IGNhc2VzIHdoZXJlIGEgZm9y
bXVsYSBvbiBvbmUgc2lkZSBvZiB0aGUgaW5zZXJ0IG9yIGRlbGV0ZSBwb2ludCBpbmNsdWRlcyBh
IHJlbGF0aXZlIHJlZmVyZW5jZSB0byBhIGNlbGwgb24gdGhlIG9wcG9zaXRlIHNpZGUgb2YgdGhh
dCBwb2ludC4gVGhlIG1vc3Qgb2J2aW91cyBpcyBhIHJlZmVyZW5jZSB0byB3aGF0IHdhcyBmb3Jt
ZXJseSBpbiB0aGUgcm93IChjb2x1bW4pIGltbWVkaWF0ZWx5IGFkamFjZW50IHRvIHRoZSBmb3Jt
dWxhIGJ1dCB0aGlzIGlzIG5vdCBieSBhbnkgbWVhbnMgdGhlIG9ubHkgaW5zdGFuY2Ugb2YgdGhp
cyBpc3N1ZSBhbmQgQ2FsYyBjYW5ub3QgYmUgZXhwZWN0ZWQgdG8gYmUgYSBtaW5kIHJlYWRlci4g
CgpJIHNvbHZlIHRoaXMgInByb2JsZW0iIGJ5IENvcHlpbmcgY2VsbHMgdGhhdCBoYXZlIG5vdCBi
ZWVuIGFmZmVjdGVkIGJ5IHRoZSBpbnNlcnQgYW5kIHRoZW4gUGFzdGluZyBvciBQYXN0aW5nIFNw
ZWNpYWwgKGZvcm11bGEpIGludG8gdGhlIHJvd3MgKGNvbHVtbnMpIHRoYXQgYXJlIG5lYXIgZW5v
dWdoIHRvIHRoZSBpbnNlcnQgcG9pbnQgdG8gbmVlZCB0aGlzIG1hbnVhbCBpbnRlcnZlbnRpb24u
IE9ubHkgYSBsaXZpbmcsIGJyZWF0aGluZywgcGVyc29uIGtub3dzIHdoYXQgcm93cyAoY29sdW1u
cykgdGhvc2UgbWF5IGJlLgoKVGlwOiBDYWxjIGRvZXMgcGVybWl0IENvcHlpbmcgY2VsbHMgYmVm
b3JlIHBlcmZvcm1pbmcgYW4gaW5zZXJ0IG9yIGRlbGV0ZSBhbmQgdGhlbiBQYXN0aW5nIG9yIFBh
c3RpbmcgU3BlY2lhbCBsYXRlci4gSSBoYXZlIGVuY291bnRlcmVkIG90aGVyIG9mZmljZSBzdWl0
ZXMgdGhhdCBkb24ndCBwZXJtaXQgdGhpcy4KCi0tIApKaW0KCi0tLS0tT3JpZ2luYWwgTWVzc2Fn
ZS0tLS0tCkZyb206IEJyaWFuIEJhcmtlciA8Yi5tLmJhcmtlckBidGludGVybmV0LmNvbT4KVG86
IHVzZXJzQGdsb2JhbC5saWJyZW9mZmljZS5vcmcKU2VudDogV2VkLCAwNCBKYW4gMjAxNyA5OjI3
ClN1YmplY3Q6IFJlOiBbbGlicmVvZmZpY2UtdXNlcnNdIENhbGMgT3B0aW9ucyA+IExpYnJlb2Zm
aWNlIENhbGMgPiBHZW5lcmFsID4gRXhwYW5kIHJlZmVyZW5jZXMgd2hlbiBuZXcgY29sdW1ucy9y
b3dzIGFyZSBpbnNlcnRlZAoKQXQgMTE6MzggMDQvMDEvMjAxNyAtMDUwMCwgQ2hhcmxlcyBNYXJj
dXMgd3JvdGU6Cj5UaGlzIGRvZXNuJ3Qgc2VlbSB0byBiZSB3b3JraW5nIGZvciBtZSwgYW5kIGhh
c24ndCBmb3IgYSBsb25nIHRpbWUsIAo+SSBqdXN0IGtlZXAgZm9yZ2V0dGluZyB0byBhc2sgYW5k
IGdvIGZpbGUgYSBidWcgaWYgaXQgaXMgYnJva2VuLiBJIAo+aGF2ZSBhIHNwcmVhZHNoZWV0IHRo
YXQgaGFzIG51bWVyb3VzIGZvcm11bGFzIGluIGNlcnRhaW4gY2VsbHMgaW4gCj5lYWNoIHJvdy4g
V2l0aCB0aGUgb3B0aW9uIHJlZmVyZW5jZWQgaW4gdGhpcyBlbWFpbCBzdWJqZWN0IGVuYWJsZWQs
IAo+d2hlbiBJIGluc2VydCBhIG5ldyByb3csIHNob3VsZG4ndCBpdCBhdXRvbWF0aWNhbGx5IHBv
cHVsYXRlIHRoZSAKPmNvcnJlc3BvbmRpbmcgY2VsbHMgaW4gdGhlIG5ldyByb3cgd2l0aCB0aGUg
Zm9ybXVsYXMgZnJvbSB0aGUgcm93IAo+YWJvdmUgKG9yIGJlbG93IGlmIEkgaW5zZXJ0ZWQgYWJv
dmUpPwoKTm8sIEkgdGhpbmsgbm90LiBJdCBzaG91bGQgZG8gd2hhdCBpdCBjbGFpbXMsIGkuZS4g
ZXhwYW5kIHJlZmVyZW5jZXMsIApub3QgZHVwbGljYXRlIGZvcm11bGFlLiBJdCdzIG5vdCByZWFs
bHkgYWJvdXQgd2hhdCBpcyBpbiB0aGUgbmV3IHJvd3MgCm9yIGNvbHVtbnMsIGJ1dCBhYm91dCBy
ZWZlcmVuY2VzICplbHNld2hlcmUqIHRvIHJhbmdlcyB3aGljaCBtYXkgb3IgCm1heSBub3QgbmVl
ZCB0byBpbmNsdWRlIHRoZW0uIElmIHlvdSBoYXZlIGEgcmVmZXJlbmNlIHNvbWV3aGVyZSB0bywg
CnNheSwgQjM6QjkgYW5kIHlvdSBpbnRyb2R1Y2UgYSBuZXcgcm93IDYsIHN1Y2ggcmVmZXJlbmNl
cyB3aWxsIAphdXRvbWF0aWNhbGx5IGJlIGV4cGFuZGVkIHRvIEIzOkIxMCBzbyBhcyB0byBpbmNs
dWRlIHRoZSBuZXcgY2VsbCBCNiAKYXMgd2VsbCBhcyB0aGUgb3JpZ2luYWwgc2V2ZW4gY2VsbHMu
IFRoaXMgYXBwbGllcyBpcnJlc3BlY3RpdmUgb2YgdGhlIApjb25kaXRpb24gb2YgdGhlIG9wdGlv
biB5b3UgaWRlbnRpZnkuCgpJZiwgYWx0ZXJuYXRpdmVseSwgeW91IGludHJvZHVjZSBhIG5ldyBy
b3cgMTAgKG9yIGEgbmV3IHJvdyAzKSAtIAphZGphY2VudCB0byBpbnN0ZWFkIG9mIHdpdGhpbiB0
aGUgZXhpc3RpbmcgcmFuZ2UgLSByZWZlcmVuY2VzIAplbHNld2hlcmUgd2lsbCBiZSB1cGRhdGVk
IG9ubHkgaWYgdGhlIG9wdGlvbiBpbiBxdWVzdGlvbiBpcyB0aWNrZWQuCgpJIHRydXN0IHRoaXMg
aGVscHMuCgpCcmlhbiBCYXJrZXIKCgotLSAKVG8gdW5zdWJzY3JpYmUgZS1tYWlsIHRvOiB1c2Vy
cyt1bnN1YnNjcmliZUBnbG9iYWwubGlicmVvZmZpY2Uub3JnClByb2JsZW1zPyBodHRwOi8vd3d3
LmxpYnJlb2ZmaWNlLm9yZy9nZXQtaGVscC9tYWlsaW5nLWxpc3RzL2hvdy10by11bnN1YnNjcmli
ZS8KUG9zdGluZyBndWlkZWxpbmVzICsgbW9yZTogaHR0cDovL3dpa2kuZG9jdW1lbnRmb3VuZGF0
aW9uLm9yZy9OZXRpcXVldHRlCkxpc3QgYXJjaGl2ZTogaHR0cDovL2xpc3RhcmNoaXZlcy5saWJy
ZW9mZmljZS5vcmcvZ2xvYmFsL3VzZXJzLwpBbGwgbWVzc2FnZXMgc2VudCB0byB0aGlzIGxpc3Qg
d2lsbCBiZSBwdWJsaWNseSBhcmNoaXZlZCBhbmQgY2Fubm90IGJlIGRlbGV0ZWQKCg==
------=_Part_1_1114535632.1483574101274
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: base64

PGRpdj5JIGhvcGUgdGhlc2UgY29tbWVudHMgYXJlIGhlbHBmdWwuPGJyLz4KPGJyLz4KSW5zZXJ0
aW5nIG9yIGRlbGV0aW5nIG9uZSBvciBtb3JlIHJvd3MgKGNvbHVtbnMpIHJlcXVpcmVzIG1hbnVh
bCBpbnRlcnZlbnRpb24gaW4gdGVybXMgb2YgcGF0Y2hpbmcgcmVmZXJlbmNlcyBpbiBtYW55IGNh
c2VzIHdoZXJlIGEgZm9ybXVsYSBvbiBvbmUgc2lkZSBvZiB0aGUgaW5zZXJ0IG9yIGRlbGV0ZSBw
b2ludCBpbmNsdWRlcyBhIHJlbGF0aXZlIHJlZmVyZW5jZSB0byBhIGNlbGwgb24gdGhlIG9wcG9z
aXRlIHNpZGUgb2YgdGhhdCBwb2ludC4gVGhlIG1vc3Qgb2J2aW91cyBpcyBhIHJlZmVyZW5jZSB0
byB3aGF0IHdhcyBmb3JtZXJseSBpbiB0aGUgcm93IChjb2x1bW4pIGltbWVkaWF0ZWx5IGFkamFj
ZW50IHRvIHRoZSBmb3JtdWxhIGJ1dCB0aGlzIGlzIG5vdCBieSBhbnkgbWVhbnMgdGhlIG9ubHkg
aW5zdGFuY2Ugb2YgdGhpcyBpc3N1ZSBhbmQgQ2FsYyBjYW5ub3QgYmUgZXhwZWN0ZWQgdG8gYmUg
YSBtaW5kIHJlYWRlci4gPGJyLz4KPGJyLz4KSSBzb2x2ZSB0aGlzICJwcm9ibGVtIiBieSBDb3B5
aW5nIGNlbGxzIHRoYXQgaGF2ZSBub3QgYmVlbiBhZmZlY3RlZCBieSB0aGUgaW5zZXJ0IGFuZCB0
aGVuIFBhc3Rpbmcgb3IgUGFzdGluZyBTcGVjaWFsIChmb3JtdWxhKSBpbnRvIHRoZSByb3dzIChj
b2x1bW5zKSB0aGF0IGFyZSBuZWFyIGVub3VnaCB0byB0aGUgaW5zZXJ0IHBvaW50IHRvIG5lZWQg
dGhpcyBtYW51YWwgaW50ZXJ2ZW50aW9uLiBPbmx5IGEgbGl2aW5nLCBicmVhdGhpbmcsIHBlcnNv
biBrbm93cyB3aGF0IHJvd3MgKGNvbHVtbnMpIHRob3NlIG1heSBiZS48YnIvPgo8YnIvPgpUaXA6
IENhbGMgZG9lcyBwZXJtaXQgQ29weWluZyBjZWxscyBiZWZvcmUgcGVyZm9ybWluZyBhbiBpbnNl
cnQgb3IgZGVsZXRlIGFuZCB0aGVuIFBhc3Rpbmcgb3IgUGFzdGluZyBTcGVjaWFsIGxhdGVyLiBJ
IGhhdmUgZW5jb3VudGVyZWQgb3RoZXIgb2ZmaWNlIHN1aXRlcyB0aGF0IGRvbid0IHBlcm1pdCB0
aGlzLjxici8+Cjxici8+Ci0tIDxici8+CkppbTxici8+PGJyLz4tLS0tLU9yaWdpbmFsIE1lc3Nh
Z2UtLS0tLTxici8+RnJvbTogQnJpYW4gQmFya2VyICZsdDtiLm0uYmFya2VyQGJ0aW50ZXJuZXQu
Y29tJmd0Ozxici8+VG86IHVzZXJzQGdsb2JhbC5saWJyZW9mZmljZS5vcmc8YnIvPlNlbnQ6IFdl
ZCwgMDQgSmFuIDIwMTcgOToyNzxici8+U3ViamVjdDogUmU6IFtsaWJyZW9mZmljZS11c2Vyc10g
Q2FsYyBPcHRpb25zICZndDsgTGlicmVvZmZpY2UgQ2FsYyAmZ3Q7IEdlbmVyYWwgJmd0OyBFeHBh
bmQgcmVmZXJlbmNlcyB3aGVuIG5ldyBjb2x1bW5zL3Jvd3MgYXJlIGluc2VydGVkPGJyLz48YnIv
PjwvZGl2PkF0IDExOjM4IDA0LzAxLzIwMTcgLTA1MDAsIENoYXJsZXMgTWFyY3VzIHdyb3RlOiYj
MTM7PGJyLz4KJmd0O1RoaXMgZG9lc24ndCBzZWVtIHRvIGJlIHdvcmtpbmcgZm9yIG1lLCBhbmQg
aGFzbid0IGZvciBhIGxvbmcgdGltZSwgJiMxMzs8YnIvPgomZ3Q7SSBqdXN0IGtlZXAgZm9yZ2V0
dGluZyB0byBhc2sgYW5kIGdvIGZpbGUgYSBidWcgaWYgaXQgaXMgYnJva2VuLiBJICYjMTM7PGJy
Lz4KJmd0O2hhdmUgYSBzcHJlYWRzaGVldCB0aGF0IGhhcyBudW1lcm91cyBmb3JtdWxhcyBpbiBj
ZXJ0YWluIGNlbGxzIGluICYjMTM7PGJyLz4KJmd0O2VhY2ggcm93LiBXaXRoIHRoZSBvcHRpb24g
cmVmZXJlbmNlZCBpbiB0aGlzIGVtYWlsIHN1YmplY3QgZW5hYmxlZCwgJiMxMzs8YnIvPgomZ3Q7
d2hlbiBJIGluc2VydCBhIG5ldyByb3csIHNob3VsZG4ndCBpdCBhdXRvbWF0aWNhbGx5IHBvcHVs
YXRlIHRoZSAmIzEzOzxici8+CiZndDtjb3JyZXNwb25kaW5nIGNlbGxzIGluIHRoZSBuZXcgcm93
IHdpdGggdGhlIGZvcm11bGFzIGZyb20gdGhlIHJvdyAmIzEzOzxici8+CiZndDthYm92ZSAob3Ig
YmVsb3cgaWYgSSBpbnNlcnRlZCBhYm92ZSk/JiMxMzs8YnIvPgomIzEzOzxici8+Ck5vLCBJIHRo
aW5rIG5vdC4gSXQgc2hvdWxkIGRvIHdoYXQgaXQgY2xhaW1zLCBpLmUuIGV4cGFuZCByZWZlcmVu
Y2VzLCAmIzEzOzxici8+Cm5vdCBkdXBsaWNhdGUgZm9ybXVsYWUuIEl0J3Mgbm90IHJlYWxseSBh
Ym91dCB3aGF0IGlzIGluIHRoZSBuZXcgcm93cyAmIzEzOzxici8+Cm9yIGNvbHVtbnMsIGJ1dCBh
Ym91dCByZWZlcmVuY2VzICplbHNld2hlcmUqIHRvIHJhbmdlcyB3aGljaCBtYXkgb3IgJiMxMzs8
YnIvPgptYXkgbm90IG5lZWQgdG8gaW5jbHVkZSB0aGVtLiBJZiB5b3UgaGF2ZSBhIHJlZmVyZW5j
ZSBzb21ld2hlcmUgdG8sICYjMTM7PGJyLz4Kc2F5LCBCMzpCOSBhbmQgeW91IGludHJvZHVjZSBh
IG5ldyByb3cgNiwgc3VjaCByZWZlcmVuY2VzIHdpbGwgJiMxMzs8YnIvPgphdXRvbWF0aWNhbGx5
IGJlIGV4cGFuZGVkIHRvIEIzOkIxMCBzbyBhcyB0byBpbmNsdWRlIHRoZSBuZXcgY2VsbCBCNiAm
IzEzOzxici8+CmFzIHdlbGwgYXMgdGhlIG9yaWdpbmFsIHNldmVuIGNlbGxzLiBUaGlzIGFwcGxp
ZXMgaXJyZXNwZWN0aXZlIG9mIHRoZSAmIzEzOzxici8+CmNvbmRpdGlvbiBvZiB0aGUgb3B0aW9u
IHlvdSBpZGVudGlmeS4mIzEzOzxici8+CiYjMTM7PGJyLz4KSWYsIGFsdGVybmF0aXZlbHksIHlv
dSBpbnRyb2R1Y2UgYSBuZXcgcm93IDEwIChvciBhIG5ldyByb3cgMykgLSAmIzEzOzxici8+CmFk
amFjZW50IHRvIGluc3RlYWQgb2Ygd2l0aGluIHRoZSBleGlzdGluZyByYW5nZSAtIHJlZmVyZW5j
ZXMgJiMxMzs8YnIvPgplbHNld2hlcmUgd2lsbCBiZSB1cGRhdGVkIG9ubHkgaWYgdGhlIG9wdGlv
biBpbiBxdWVzdGlvbiBpcyB0aWNrZWQuJiMxMzs8YnIvPgomIzEzOzxici8+CkkgdHJ1c3QgdGhp
cyBoZWxwcy4mIzEzOzxici8+CiYjMTM7PGJyLz4KQnJpYW4gQmFya2VyJiMxMzs8YnIvPgomIzEz
Ozxici8+CiYjMTM7PGJyLz4KLS0gJiMxMzs8YnIvPgpUbyB1bnN1YnNjcmliZSBlLW1haWwgdG86
IDxhIGhyZWY9Im1haWx0bzp1c2Vycyt1bnN1YnNjcmliZUBnbG9iYWwubGlicmVvZmZpY2Uub3Jn
Ij51c2Vycyt1bnN1YnNjcmliZUBnbG9iYWwubGlicmVvZmZpY2Uub3JnPC9hPiYjMTM7PGJyLz4K
UHJvYmxlbXM/IDxhIGhyZWY9Imh0dHA6Ly93d3cubGlicmVvZmZpY2Uub3JnL2dldC1oZWxwL21h
aWxpbmctbGlzdHMvaG93LXRvLXVuc3Vic2NyaWJlIj5odHRwOi8vd3d3LmxpYnJlb2ZmaWNlLm9y
Zy9nZXQtaGVscC9tYWlsaW5nLWxpc3RzL2hvdy10by11bnN1YnNjcmliZTwvYT4vJiMxMzs8YnIv
PgpQb3N0aW5nIGd1aWRlbGluZXMgKyBtb3JlOiA8YSBocmVmPSJodHRwOi8vd2lraS5kb2N1bWVu
dGZvdW5kYXRpb24ub3JnL05ldGlxdWV0dGUiPmh0dHA6Ly93aWtpLmRvY3VtZW50Zm91bmRhdGlv
bi5vcmcvTmV0aXF1ZXR0ZTwvYT4mIzEzOzxici8+Ckxpc3QgYXJjaGl2ZTogPGEgaHJlZj0iaHR0
cDovL2xpc3RhcmNoaXZlcy5saWJyZW9mZmljZS5vcmcvZ2xvYmFsL3VzZXJzIj5odHRwOi8vbGlz
dGFyY2hpdmVzLmxpYnJlb2ZmaWNlLm9yZy9nbG9iYWwvdXNlcnM8L2E+LyYjMTM7PGJyLz4KQWxs
IG1lc3NhZ2VzIHNlbnQgdG8gdGhpcyBsaXN0IHdpbGwgYmUgcHVibGljbHkgYXJjaGl2ZWQgYW5k
IGNhbm5vdCBiZSBkZWxldGVkJiMxMzs8YnIvPgomIzEzOzxici8+Cg==
------=_Part_1_1114535632.1483574101274--

#4

*Without "expand references"*
=SUM(Sheet1.A1:A99)
becomes
=SUM(Sheet1.A2:A100)
after you inserted one new row at Sheet1, row #1. The reference moves down
one row.
When you insert one row between row #2 and row #99 the reference expands by
one row:
=SUM(Sheet1.A1:A100)
Nothing happens when you insert directly below A1:A99 in row #100.

*With "expand references"*
=SUM(Sheet1.A1:A99)
becomes
=SUM(Sheet1.A1:A100)
when you insert one row anywhere between row #1 and row #100 directly below.

This applies to all formula expressions with references, namely cell
formulas, conditional format, named ranges, database ranges, chart areas,
pivot table sources, validation conditions, conditional format conditions
and form control sources (list box, combo box).

In 99% of all use cases you want this option being checked. Sometimes it can
be annoying and then it is good to know how to turn off.

#5

It does, thanks. I understand now what that option does.

Now I'm off to create a new feature request to add another option to
auto-add formulas when a row is inserted between two other rows that
have filled formulas in them.

Question - is there a term for when two rows (or columns) have -
adjacent - formulas that were filled up/down/across?

#6

Calc is dying of featuritis.
You might be interested in a macro of mine.
[Calc, Python] Expand/shrink list ranges
<https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=2350>