[libreoffice-users] Re: Calc Options > Libreoffice Calc > General > Expand references when new columns/rows are inserted
*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
[libreoffice-users]
postmas...@btinternet.com) Subject: Re: [libreoffice-users] Calc Options > Libreoffice Calc > General > 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
Re: [libreoffice-users] Re: Problems exporting and importing a dialogue
Peter, Understood. Thanks. Gordon. On 04/01/17 23:22, ptoye wrote: Gordon, I am referring specifically to a Dialog(ue), not to text within a file. - Peter -- View this message in context: http://nabble.documentfoundation.org/Problems-exporting-and-importing-a-dialogue-tp4204239p4204319.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
Re: [libreoffice-users] Calc Options > Libreoffice Calc > General > Expand references when new columns/rows are inserted
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. Brian Barker -- 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] Calc Options > Libreoffice Calc > General > Expand references when new columns/rows are inserted
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 -- 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] Conditional formatting and named ranges issues.
I've run into a couple of issues using conditional formatting and using conditional formatting with names ranges. 1. I have a range that includes 4 different columns with conditional formatting. Everything is fine. I insert a row to a a new record in its proper place in the order, and it ends up adding a number of extra conditional formats. Some are just the range from beginning to row before, and then row after to end, but many are single cell entries? I can generally fix it by using the manage option, and removing the bad ones, and editing the ranges for the good one. Would just expect the condititonal formatting to handle row inserts and just keep the original start and just increase the end by rows inserted. 2. In a couple of the conditions, I do a vlookup to check if the id is in a table, and set the color based on if it is or isn't. Used a range name since the list can change. Recently added some records to the list, and changed the name range, but the conditional formatting did not pick it up. I had to manually edit the conditional formatting, and hardcode the absolute address of the range. Even tried deleting and recreating the range, and it still seemed to use the old one. Is there a step I am missing. Thanks. +--+ Michael D. Setzer II - Computer Science Instructor Guam Community College Computer Center mailto:mi...@kuentos.guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +--+ http://setiathome.berkeley.edu (Original) Number of Seti Units Returned: 19,471 Processing time: 32 years, 290 days, 12 hours, 58 minutes (Total Hours: 287,489) BOINC@HOME CREDITS ABC 16613838.513356 | EINSTEIN 124037953.788695 ROSETTA 54952261.295224 | SETI97772491.790659 -- 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: Why is a custom menu item greyed out?
OK, I've found the answer from someone on the LO Ask forum (why are there two?). It seems that LO doesn't allow you to click on a menu item unless there's an action associated with it. Seems sensible, but I was designing the user interface before writing the macros. Once I'd added the actions all was well, so we can close this thread. - Peter -- View this message in context: http://nabble.documentfoundation.org/Why-is-a-custom-menu-item-greyed-out-tp4204211p4204323.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: Why is a custom menu item greyed out?
Virgil, Odd. It seems that your experience is the opposite of mine, but I don't have a Linux system that I can try it out on. - Peter -- View this message in context: http://nabble.documentfoundation.org/Why-is-a-custom-menu-item-greyed-out-tp4204211p4204321.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: Problems exporting and importing a dialogue
Regina, See my comments below. Best regards, and a successful new year, Peter mailto:l...@ptoye.com www.ptoye.com - Wednesday, January 4, 2017, 12:31:35 AM, you wrote: Hi Peter, ptoye schrieb: > I wanted to transfer a dialogue from a text document to the live version. So > I went into the test doc "organise dialogs|Edit" window and exported the > dialogue, which was called "OneOff" How do you make it 'called "OneOff"'? Originally I called it "One-Off" when I first created it with the Tools|Macros|Organise Dialogues menu. Later I changed it to "OneOff" by clicking on its name in the macro organiser window. , to a file. Went into the live version, > found that I had to edit a dummy dialogue before I could import the saved > one, The document needs to have a library (likely 'Standard') and at least one module. Then you have an 'Import Dialog' in the toolbar in the module in the IDE. Because you need the module anyway for the macros to execute the dialog, first generate the module and then insert the dialog. I admit that I hadn't noticed that the "Import Dialogue" was available in the macro editor as well as the Dialogue Editor. imported the file. And found that the new dialogue was called > "One-Off", which had been its name some days ago. I looked at the exported > .xdl file and there's a section saying So I have 2 questions/comments: > > 1) Why is the imported file getting a different name? Alternatively, why has > the exported file got an out-of-date ID? In my tests it gets the name of the tab in the Basic-IDE as value of the attribute 'dlg:id' and on import this value is used in the Basic-IDE as tab name. If such tab already exists, you get the option to rename or replace. So it seems that the dlg:id attribute isn't changed when the name is changed. This looks like a bug to me (not an important one). > > 2) Is there a way of importing a dialogue without having to edit a dummy > dialogue? See above, you need a module. BTW, I find is useful to have an own toolbar for macros. I use the commands 'Edit Macros' = .uno:BasicIDEAppear (from category Application) and name it 'IDE'. 'LibreOffice Basic' = .uno:MacroOrganizer (from category BASIC) and name it 'Run' 'LibreOffice Basic Macro Organizer' = .uno:MacroOrganizer (fro, category BASIC) and name it 'Organize' That make access quicker than via Tools > Macros. Thanks - I'll try this. Kind regards Regina - Peter -- View this message in context: http://nabble.documentfoundation.org/Problems-exporting-and-importing-a-dialogue-tp4204239p4204320.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: Problems exporting and importing a dialogue
Gordon, I am referring specifically to a Dialog(ue), not to text within a file. - Peter -- View this message in context: http://nabble.documentfoundation.org/Problems-exporting-and-importing-a-dialogue-tp4204239p4204319.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: Problems exporting and importing a dialogue
Gabriele, A Dialogue (or Dialog if you like the US spelling) is a feature of Libre Office which does not appear to be documented anywhere in the User Guides. It allows you to display a window with controls (much like a form) so that the user can interact with the document at runtime. Look in the LO help files under "Macros and programming|Guides" where there is some (but not a lot of) useful information. I use a dialogue in Calc to allow the user to enter values which, when an "OK" button is pressed, are entered into the correct cells in a spreadsheet using macro code in LO Basic. But it's not confined to Calc - you can write a dialogue in any LO application. HTH - Peter -- View this message in context: http://nabble.documentfoundation.org/Problems-exporting-and-importing-a-dialogue-tp4204239p4204318.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
