[libreoffice-users] Re: Calc Options > Libreoffice Calc > General > Expand references when new columns/rows are inserted

[Andreas Säger]

Calc is dying of featuritis.
You might be interested in a macro of mine.
[Calc, Python] Expand/shrink list ranges
  



--
View this message in context: 
http://nabble.documentfoundation.org/Calc-Options-Libreoffice-Calc-General-Expand-references-when-new-columns-rows-are-inserted-tp4204347p4204553.html
Sent from the Users mailing list archive at Nabble.com.

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

[libreoffice-users] Re: Calc Options > Libreoffice Calc > General > Expand references when new columns/rows are inserted

[Tanstaafl]

On Wed Jan 04 2017 12:26:42 GMT-0500 (Eastern Standard Time), Brian
Barker  wrote:
> At 11:38 04/01/2017 -0500, Charles Marcus wrote:
>> 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)?
> 
> 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.

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?

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

[libreoffice-users] Re: Calc Options > Libreoffice Calc > General > Expand references when new columns/rows are inserted

[Andreas Säger]

*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.



--
View this message in context: 
http://nabble.documentfoundation.org/Calc-Options-Libreoffice-Calc-General-Expand-references-when-new-columns-rows-are-inserted-tp4204347p4204393.html
Sent from the Users mailing list archive at Nabble.com.

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