Ask Your Question

AllanR's profile - activity

2019-01-01 14:39:10 +0200 edited answer Italics auto formatting

Thanks, exactly what I needed to know as well. Oops sorry , should have made this a comment.

2019-01-01 14:37:17 +0200 answered a question Italics auto formatting

Thanks, exactly what I needed to know as well.

2018-09-26 21:47:50 +0200 commented answer Is it possible to add text to a chart in Calc

Exactly the info I was looking for, thanks.

2017-11-02 05:41:06 +0200 received badge  Famous Question (source)
2017-11-02 05:41:06 +0200 received badge  Notable Question (source)
2017-01-20 08:18:55 +0200 received badge  Famous Question (source)
2016-03-22 18:04:02 +0200 received badge  Famous Question (source)
2016-01-09 23:30:29 +0200 received badge  Famous Question (source)
2015-08-26 21:16:58 +0200 received badge  Notable Question (source)
2015-08-26 21:16:58 +0200 received badge  Popular Question (source)
2015-07-16 06:18:35 +0200 received badge  Notable Question (source)
2015-07-16 06:18:35 +0200 received badge  Popular Question (source)
2015-05-24 18:33:48 +0200 received badge  Notable Question (source)
2015-05-24 18:33:48 +0200 received badge  Popular Question (source)
2015-05-24 12:58:32 +0200 asked a question Why does Text Background In Draw Object Become Permanently White?

I'm puzzled by the following behaviour of text in a Draw object - rectangles, circles, text boxes etc..

  • At the start of a new document, object text behaves correctly i.e. the text is black on top of whatever object area color is set. All is fine
  • After adding other elements and much editing, ALL previously correct objects "suddenly" display a white background to the text.
  • It's not obvious what causes it or when, but once it occurs I can't find a way to correct it. It happens with all the background types - color, hatching, gradient and bitmap.
  • The problem seems to be associated with the document itself, because copy/pasting any of the objects into a new document fixes the problem, and copying a good object into the problem document re-introduces the issue.

Am I missing some setting or other technique to fix? I don't have enough points to show good/bad examples.

Using LO 4.4.2.2 with Win 7.

2015-04-10 17:37:25 +0200 answered a question Calc- problem linking to data in another spreadsheet.

It's not as bad as it seems.

Yes, Excel does resolve any formulae and cell references with respect to the source file, and presents the result (value) to the target file. This is probably the most common requirement when linking cell(s) between spreadsheets and so pleases most.

CALC only works like Excel for source cells which contain text/numerics/self-contained formulae. If the linked cells contain absolute cell addresses, it appears to fail. This is because CALC copies any sheet and cell references (in the source cell) and resolves them within the target file. This is not what most people expect and consider it a fault.

However, it would suit the situation where you want to hold a master formula in just one spreadsheet, but operate on multiple sets of data items in other sheets/files. By changing just one formula, all sheets/files could use the new version.

To achieve more Excel-like results you can:

1) Use a DDE link from the Target to the Source (enter "=" into target cell, navigate to source cell and Left-click mouse). It's the easiest to implement but it creates an absolute address to the source cell (e.g. $F$10). Thus if the source cell changes position (e.g. rows inserted above) the wrong data will be reported. Updates at file-open or Ctrl+Shift+F9

2) Use the Insert > Link to External Data (as in the 1st answer) in the target cell and select the source file, then "Named Area". This creates a relative link which includes any formatting. Points to note though:

a) The source cell requires to be named before linking.

b) At the time of link set-up, set the update frequency off/on and in seconds, useful where data changes rapidly (note Ctrl+Shift+F9 does not work with this link). This is in addition to the normal refresh at file-open.

c) The source cell should not contain any absolute cell addresses e.g. B1 or $A$3. Instead, use named areas in any formulae in cells you wish to link to. OR .....

d) A workaround to avoid having to give names to many absolute cell addresses in the source, is to create a new cell just for linking to (rem to give a name!), and in it simply put "=<actualcellname>" so that it points to the "actual cell name" containing the formulae with the absolute addresses. Behind the scenes CALC takes a snapshot of all the source cell-names and their absolute cell addresses and stores them in the target file for subsequent use. These can be observed in the Names Manager (Ctrl+F3) of the target file, with prefix file:/// in the Range column. Note these absolute addresses require updating if any cells are moved in the source sheet. If this happens, simply delete all the file:/// entries in the Names Manager (Ctrl+F3) of the target file, and they are automatically re-created at the next file-open links-update action.

2015-04-10 17:28:27 +0200 commented answer Calc- problem linking to data in another spreadsheet.

Comment 1 is true to a degree, but there are ways to correct this. See my answer. AllanR

2015-04-10 15:12:57 +0200 received badge  Famous Question (source)
2015-04-10 14:20:48 +0200 asked a question CALC / CHART Range Fill Color and Legend behaviour seems odd

I'm trying out the "Range for Fill Color" feature, and finding odd results.

In a simple pie chart example, the pie colors are changing correctly, however the legend colors remain at the automatically assigned values. To get the legend colours to match, I have to manually select each data point and select the same color as used by the Fill Color Range.

This seems counter productive and negates the benefit of the Range Fill Color function? (which I find very useful)

Here's a simple example: Chart_Colour_Fill.ods

Is this a bug or am I misunderstanding the technique?

Using LO 4.4.0.3 on Win7.

2015-03-30 10:53:47 +0200 received badge  Popular Question (source)
2015-03-29 22:45:41 +0200 answered a question Libreoffice Writer - Most chapters missing from table of contents??

By default, paragraph styles Heading 1, Heading 2 etc are pre-set in Tools > Outline Numbering for use as chapters.

Styles Contents 1, 2, 3 .... are normally used to format the appearance of the entries in the Table of Contents.

It's the Level number allocated to a paragraph style (in Tools > Outline Numbering) that determines what appears in the TOC, in conjunction with the "evaluate up to level", when you right-click on the TOC and select "Edit/Index Table".

By using the Contents 1 style for chapter formatting, you may need to adjust Tools > Outline Numbering to reflect this fact or re-jig using the default settings?

EDIT TO ADD: Another thought: I was caught out with missing TOC entries when I used paragraph styles which simply created Act 1, Scene 1, Scene 2 ....., Act 2, Scene 1, Scene 2 .... headings. I didn't add (or need) any heading tecxt of my own. I discovered however that you HAVE to enter some text (manually) at each heading in the document (a space character will suffice) otherwise the heading won't appear in the TOC.

2015-03-29 22:20:25 +0200 commented answer Libre Office Dictionaries Path Cannot Be Changed

Alas, I've tried the portable versions of LO 4.3.6 and 4.4.1 and they also exhibit the problem that an alternative Dictionary path won't stick - tested on Win 7 and XP. Perhaps I should raise as a bug?

2015-03-28 20:03:42 +0200 asked a question Libre Office Dictionaries Path Cannot Be Changed

I'm trying to change the default Writer dictionaries path, to allow them to be shared more easily with other computers.

The process at first appears to work - Tools > Options > Paths > Dictionaries > Edit > Add > Select Path > Click on radio button > OK.

If I double-check the settings immediately, the original and the new path are listed correctly. However after closing Writer and re-starting, the alternate dictionary path is missing.

I've tried changing all the other paths and they DO work ok - it's just the Dictionaries path that refuses to change.

Anyone else experiencing this or am I missing something? Using LO 4.4.0.3 on Win 7.

2015-03-26 23:05:13 +0200 answered a question Page numbering for a stage play

To create a page number system of Act # - Scene # - page #, do the following:

1) Use/configure paragraph style Heading 1 for the Acts, and Heading 2 for the Scenes.

2) From Tools > Outline Numbering, select Roman numerals for Level 1 and Arabic for Level 2.

3) Insert into the page footer the Act, Scene and Page Number fields.

..a) For Act#, use Insert > Fields > More Fields > Document tab.

.. b) Select Type= Chapter and Format= "Chapter Number without Separator" (or other to suit), and set Level= 1

..c) Repeat step 3 to insert Scene# except using Level= 2

4) Finally insert page# via Insert > Fields > Page Number.

5) Job done. It's not as difficult as it sounds.

I've provided a working example of a play to demonstrate the technique.

2015-03-20 18:20:06 +0200 received badge  Notable Question (source)
2015-03-10 13:18:25 +0200 commented answer How to detach a template?

Here's another (simple) thought .... Open for editing your original invoice Template and save-as type .odt to a folder outwith LO’s reach, e.g. where you keep your data files. This new “working master” document will have no template link and can be used as the original to create specific invoices. If you change the Template in the future, re-create a new “working” invoice. Existing invoices will be unaffected.

2015-03-10 13:15:20 +0200 received badge  Editor (source)
2015-03-09 19:31:43 +0200 asked a question Shapes Created In Draw Disappear / Vanish After Copy Into Writer Frame

Everything appears to work fine during editing and save of a Writer document. But on reloading (File > Reload), several shapes used in diagram frames have 'disappeared'. After some experimentation I've established: (Using LO 4.4.0.3 in Win7)

1) DRAW shapes all appear ok when first copied into a WRITER frame.

2) Certain DRAW shapes (circle, rectangle etc) are missing from the frame after re-loading the WRITER document. Note DRAW lines and text boxes are unaffected.

3) Using "Arrange > Bring To Front" has no affect.

4) A single DRAW shape e.g. circle copied to a frame OR to page, PERMANENTLY disappears after reload.

5) Two or more shapes copied over (as a group) do re-appear after reload but are ALWAYS behind any frame area fill, hence only visible if transparency >~10%

Anyone else experienced this issue?

2015-03-09 13:05:29 +0200 answered a question How to detach a template?

Page 312 of Writer 4.2 Guide says: "Updating a document from a changed template....... If you choose Keep Old Styles in the message box shown in Figure 307 after changing the template, and then at some stage save the document, then that message will not appear again the next time you open the document. You will not get another chance to update the styles from the template, although you can use the macro given in the Note below to re-enable this feature."

So I think your invoice will be permanently disconnected from the template if you elect to not update.

EDIT: Here's another (simple) thought .... Open for editing your original invoice Template and save-as type .odt to a folder outwith LO’s reach, e.g. where you keep your data files. This new “working master” document will have no template link and can be used as the original to create specific invoices. If you change the Template in the future, re-create a new “working” invoice. Existing invoices will be unaffected.

2015-03-07 13:06:17 +0200 received badge  Enthusiast
2015-02-25 21:46:17 +0200 commented answer Libre Calc: Cannot Add a Hyperlink to Cell Containing a Formula

I've subsequently raised a bug report Bug 89633 and enhancement request bug 89572 to hopefully allow CALC to handle Excel hyperlinked cells properly.

2015-02-23 11:16:29 +0200 received badge  Popular Question (source)
2015-02-22 21:59:40 +0200 commented answer Libre Calc: Cannot Add a Hyperlink to Cell Containing a Formula

Thanks Karolus, that formula works well - it also accepts a Named area, and permits the hyperlinked cell to be used in other formulas.

2015-02-21 20:40:43 +0200 asked a question Libre Calc: Cannot Add a Hyperlink to Cell Containing a Formula

LibrOffice Calc : Adding Hyperlink to Cell Containing a Formula

I'm attempting to migrate my Excel spreadsheets over to LibreOffice Calc but I'm having difficulty with the cells which contain a numeric value or formula (e.g. price * VAT) AND a hyperlink (e.g. to the supplier's website). In Excel these cells can still be used for calculations e.g. =SUM, or to show price including sales tax (value*1.20), in addition to being hyperlinked.

Calc doesn't seem to be able to do this and I have found two major problems trying to use the "price" cells:

Am I missing something fundamental here? I'm using the current version (4.4.0.3) on a Win7 PC.

THE DETAILS :

1) When the Excel spreadsheets are opened in Calc, everything looks and and seems to function ok, however subsequent editing is problematic. a) The original URL hyperlink cannot be edited because the Hyperlink dialogue box opens with no values. b) You cannot select "price" cells with left mouse-click. You have to click on any unused/empty cell then navigate to the price cell using the keyboard arrow keys, then edit the price or formula using the Input Line.

2) Creating a hyperlinked "price" cell in Calc doesn't seem possible because: a) Applying a hyperlink to a cell appends existing content with the URL text, therefore is no longer a value. b) Applying a hyperlink to a value in the cell (double-click cell and highlight the value) adds the hyperlink but again results in text. Thus the cell cannot be used in any calculations. c) Applying a hyperlink to a formula in a cell e.g. =15.6*1.2 simply doesn't work.

Here's a simple spreadsheet to illustrate what I mean. It was created as an Excel.xls then opened in Calc and re-saved as .ods https://www.dropbox.com/s/04rn6niabmt...

The only workaround I can see is to insert an extra column alongside each price column just to contain the hyperlink. However this would involve editing EVERY price entry which is not feasible.