We will be migrating from Ask to Discourse on the first week of August, read the details here

 2014-11-03 02:13:12 +0200 commented answer Using VLOOKUP provides false information You've got it. If you want to ensure that you are exactly matching the SearchCriterion with a value in the lookup Array, always enter 0 for the last argument. 2014-10-20 08:16:00 +0200 commented question How to import microsoft excel 10 to libreoffice calc You should be able to open an Excel spreadsheet directly in LibreOffice. Can you give more detail on the process you are following to import to LibreOffice. And if possible an example file would also help. 2014-10-15 01:12:01 +0200 answered a question How to get the equation of a trend line in Calc? To extract the trend line data from a series of x an y values, you can use the LINEST function. This function returns the values for s and b from your equation above. It can also return other tests such as the r squared value. Because LINEST returns multiple results, it must be entered as an array formula. You do this by pressing Ctrl+Shift+Enter when you finish entering the formula. The results are returned to multiple cells. Approximately 2 columns wide by 5 rows high. LINEST takes 4 arguments. 1 a list of Y-Values, 2 a list of x-values, 3 enter TRUE to force the vertical axis intercept to 0 or false for it to be calculated. 4. Enter TRUE to return regression statistics and FALSE to only return the values for a and b. let us know if you have any questions. Cheers, John 2014-10-07 10:39:41 +0200 received badge ● Enthusiast 2014-10-06 11:05:21 +0200 received badge ● Editor (source) 2014-10-06 11:04:12 +0200 answered a question LO 4.3 calc template to fill in date but never change it after it has been filled in TODAY is brilliant when you need formulas to constantly recalculate based on the current date. However there are many scenarios where once the date has been entered, you would like it to become static There is a keyboard shortcut to enter a static version of the current date (Ctrl + ;) However, this would still require users to remember to carry out this step. Unfortunately, to have a static version of the current date automatically applied to a new instance of your receipt template would require a macro. Regards, John As you requested, I have added a simple macro below which you can use to enter the current date just once: Sub insertCurrentDate Dim oDoc as Object Dim oSheet as Object DIm oCell as Object oDoc = ThisComponent oSHeet = oDoc.Sheets.getByName("Sheet1") oCell = oSheet.getCellRangeByName("B3") If oCell.Type = EMPTY Then oCell.Value = Now End sub  Basically the macro checks whether the cell B3 is empty and uses the Now function to add the current date and time to the cell when it is empty. If you have any questions about the code let me know. Modify the settings such as the sheet and cell names to fit your requirements. To make this macro run automatically first you need to add the code to a macro module in your workbook. Once the code has been added, the second step is to set up an event that triggers the macro to run. There are many events such as opening, saving, closing and printing that can all trigger macros. To run the macro, every time the file is opened carry out the following steps: In the Calc window, choose Tools > Customize... Select the Events tab in the Customize dialog box Select the Open Document event, then click the Macro... button In the Library list of the Macro Selector dialog box, navigate to the module that contains the macro code. This should be stored under your file name. Select the insertCurrentDate macro from the Macro name list and click OK. Click OK in the Customize dialog box. Your insert current date is now ready to run whenever the file is opened. Of course once the date has been entered the first time, the date cell is no longer empty and the command that enters the date into the cell is skipped. Download the file at the following link to see a working example. CurrentDateMacro.ods Cheers, John 2014-10-06 10:48:41 +0200 answered a question How to make 3 different cells in the same row enter pre-scripted text automatically Hi. When working with lookup functions I find it easier to think of the last argument as match type. When you set the argument to 0, VLOOKUP requires an exact match. If there is any difference between the value you are looking up, and it's corresponding value in the lookup table, a match will not be found. Things to look out for when VLOOKUP returns an unexpected #N/A error: Are the ranges in the lookup table correct? This one can still catch me out on a hectic day if I have forgotten to add $ or a range name to the lookup table reference. Are you comparing apples with apples? Lookup functions don't recognize the number 1234 as matching the text "1234". When you need to match numeric codes make sure all codes (in both the lookup table and the report) have been converted to numbers or all codes have been converted to text. It doesn't matter which as long as the data types match. Watch out for orphaned spaces at the end of a lookup value. These can cause havoc with lookup functions as they prevent matches from being found and are invisible to the naked eye. If you suspect this may be the issue, the TRIM function can be used to remove excess spaces from text. Hope this helps solve the #N/A woes. Cheers, John 2014-09-23 11:00:13 +0200 commented answer When I try to sort my table using column 2 as the basis for sorting in descending order, I get a mixed up hodgepodge of numbers. @ROSt52 Thanks for the feedback. 2014-09-22 04:53:25 +0200 answered a question When I try to sort my table using column 2 as the basis for sorting in descending order, I get a mixed up hodgepodge of numbers. Another check to perform, especially if you are sorting in Calc: Does the column you wish to sort by contain formulas? If so, after sorting, the cells in the sorted column may end up pointing to different cells than they did before sorting which will then recalculate and the values in the sorted column will no longer be in the desired order. Cheers, John 2014-09-21 02:26:01 +0200 answered a question how to adjust a x and y axis ranges on a chart created in Calc To adjust chart ranges, you first need to be in chart edit mode. To edit an existing chart, either double - click on the chart or right-click on the chart and select Edit from the pop - up menu. Once in edit mode, right-click on the chart and choose Data Ranges... from the pop up menu. In the Data Ranges dialog box you can adjust settings for the entire data range in the Data Range tab or make customised adjustments in the Data Series tab. To modify a standard chart such as column or line in the Data Series tab, adjust the x-axis range with the Categories text box. Adjust the y-axis range by selecting Y-Values in the Data ranges: list then change the range in the Range for Y-Values text box. For an x-y scatter chart the x and y ranges appear as individual entries in the Data ranges: list. Select the item you wish to adjust from the list and then change the entry in the Range for... text box. Cheers John 2014-09-21 01:36:41 +0200 answered a question What is the point of Ctrl-A Select All in Calc? As you said in your question, Ctrl+End takes you to the last used cell in the sheet. If you press Shift at the same time (Ctrl+Shift+End) you select all the cells between your current position and the last used cell in the sheet. Cheers John 2014-07-21 14:10:04 +0200 received badge ● Nice Answer (source) 2014-07-20 18:38:02 +0200 received badge ● Supporter (source) 2014-07-20 12:49:49 +0200 answered a question Floating column chart? Hi. In the attached file i have created a basic floating chart. C:\fakepath\ChartTest.ods With all specialist charts such as this one the key is the organisation and manipulation of your data. If the minimum value is less than zero then display the min value. If it is greater than zero then you need a spacer to position it in the right location. To position the maximum in the right location just display the maximum if the min is below zero otherwise you need to calculate the difference between the min and the max. To create this chart I used three calculated data series. 1. The first series is the spacer for the min value. This needs to be be zero if the low is below zero or the same value as the low. The formula is =MIN(0;LowCell). 2. The second to display the low value. This is the low value if below 0 or it must be zero as the spacer has positioned the bottom of the column for low values above 0. The formula is =MIN(0;LowCell). 3. The third series displays the top of the bar. The High value. This is the High value when the low value iss below zero and the difference between the high and low value when the low is above zero. The formula is = HighCell-MAX(0,LowCell). Once the series are set up, inset a stacked column chart. Then the rest is formatting. 1. Remove borders for all series. 2. Set the first series Area Fill to None. 3. Give the second and third series the same Area Fill colour. 4. Change the location of the horizontal axis labels. Select Outside Start for Place Labels in the PositionIng tab of the format axis dialog. Hope this helps. Chers, John 2014-07-17 11:25:00 +0200 answered a question How to replace all commas with dots in libreoffice calc but only in certain cells Hi Robert, Depending on how you import those tables and your Locale settings those changes should be handled automatically. However if the commas aren't converted and you need to import those tables on a regular basis then I would recommend using a formula to do the job. =SUBSTITUTE(A1;",";"") will remove the commas from the number in A1. Another issue that may occur is that the number 1000 represented as 1,000 will become 1 after import as the comma is now acting as a decimal point. If this occurs you will need to multiply values by 1000. Without seeing an example of the tables it is difficult to visualise the best approach. Cheers, John 2014-07-03 09:29:45 +0200 answered a question Find/replace only first word in every paragraph Since your data is in Calc you can use the following formula to convert the first space into a line break: =SUBSTITUTE(A1;" ";CHAR(10);1) The SUBSTITUTE function searches for a piece of text in a cell and replaces it with another piece of text. SUBSTITUTE has 4 arguments. 1) Cell or piece of text to search in. I this example i assume your original name is in A1. 2) Piece of text to search for. Always enclose text in speachmarks in formulas. There is a space between the speachmarks in the above formula. 3) The new text to replace the item found in argument 2. The above formula uses CHAR(10) to create a line break. The CHAR function returns the character represented by the character code enclosed in the brackets. I believe 10 is the code for a carriage return which produces a line break in the cell. 4) the last argument is possibly the most important in your situation. It represents which instant of argument 2 you wish to replace. If left blank, it replaces every instance of argument 2 it finds. By setting this argument to 1 only the first space in the name is replaced with a line break. Once you have created the formula and copied it down your data, you can then copy the formula cells and use the Pasre Special... dialog to paste as text over the original values. Personally i prefer to keep the original data column in tact in case the data needs to be changed. Cheers, John 2013-05-11 22:11:39 +0200 answered a question How to set line height for entire document? Note that to be able to implement the above solution you must first turn on Asian language support. Otherwise the Text Grid tab wont appear in the Page Style dialog. To turn on Asian language support. Choose Tools > Options. Under Language Settings choose Languages. Then below the Enhanced language support heading, select Show UI elements foe East Asian writings. 2013-04-30 07:08:45 +0200 received badge ● Teacher (source) 2013-04-29 11:37:28 +0200 answered a question Multiple sheets on LibreOffice Calc Another option is to write a formula that returns an empty string when the cell in the first sheet is empty. Instead of the formula =$sheet1.d15 You could use =IF($sheet1.d15=0,"",$sheet1.d15) The If function performs a logical test. When the result of the test is true one value is returned. When the result is false a different value is returned. In the above formula the test is $sheet1.d15=0. When this test is true you dont want 0 to be displayed on the second sheet so in stead you return an empty string using "". When the test is false you want the value to be displayed in sheet two so you return$sheet1.d15.