Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 02 Jun 2019 02:42:30 +0200Calc: How show formulas next to valueshttps://ask.libreoffice.org/en/question/195900/calc-how-show-formulas-next-to-values/I would like to show values in one column and show the formulas for deriving those values in an adjacent column. How do I do that?catbillSun, 02 Jun 2019 02:42:30 +0200https://ask.libreoffice.org/en/question/195900/Equal Sign Sum of Cells Doesn't Workhttps://ask.libreoffice.org/en/question/187387/equal-sign-sum-of-cells-doesnt-work/To get the sum of a series of numbers in cells in a column, I place my cursor inside the cell below the series, then I click on the Equal sign (=) up on the tool bar.
Then I place my cursor in the top cell of the series and drag down to include all the cells.
Instead of getting the sum, I get #VALUE!
When I look at the field for the cell in the tool bar, I see =A1:A8, and that's what shows in the cell when I place the cursor in the field. But when I hit Enter, I again get #VALUE!
What am I doing wrong?librenutzTue, 19 Mar 2019 04:12:33 +0100https://ask.libreoffice.org/en/question/187387/[SOLVED]Finding matching valuehttps://ask.libreoffice.org/en/question/180300/solvedfinding-matching-value/ Hello!
I have trouble figuring out how to get the corresponding value for cell H11 to appear in cell I11
![Example](https://i.imgur.com/ehjaCvR.png)
I want to automatically (i. e. that if column is extended, the new year for the new MAX value is inserted in I11)
insert the year in which the MIN/MAX value for a column is reached in cell I11.
What I mean is that in this example the max value for column E is in E3, now I want the matching year 2005 to appear in cell I11.
I (unsuccessfully) tried something like *INDEX(B3:B17;MATCH(E3:E17;H11;0))* for I11.
Any ideas/help to fix my problem is appreciated!
~ Laniakea
LaniakeaWed, 23 Jan 2019 09:49:11 +0100https://ask.libreoffice.org/en/question/180300/How to link the content of two row in a columnhttps://ask.libreoffice.org/en/question/183328/how-to-link-the-content-of-two-row-in-a-column/ Hi everyone
I would like to know how to link the content of several row (consisting of a constant letter plus a variable number) scattered in a very long column A at the end of the value of the one just below and possibly spaced.
Here an explenation:
![image description](/upfiles/15502474026284756.png)
Thank you in advance,
Mario
marlomFri, 15 Feb 2019 17:18:22 +0100https://ask.libreoffice.org/en/question/183328/Find returns #value! if search string not found. How to get around that?https://ask.libreoffice.org/en/question/44565/find-returns-value-if-search-string-not-found-how-to-get-around-that/When using either FIND("FindText"; "Text"; Position) or SEARCH("FindText"; "Text"; Position), if the FindText is within the Text, it returns the position where it is found. But if the FindText is not within the Text, it returns #VALUE!. Shouldn't it return 0? How to get around this problem? Thanks.LWMcQWed, 07 Jan 2015 00:54:24 +0100https://ask.libreoffice.org/en/question/44565/How to convert string from calc database to value in conditionhttps://ask.libreoffice.org/en/question/178413/how-to-convert-string-from-calc-database-to-value-in-condition/I want to read a value from a mailmerge database (in this case an .ods file). Dependant on whether the value is >0 or <0 I want to substute different strings. The comparison in the condition field works only, if I compare to strings. I don't find a way to convert a string from a field within an .ods file (where it is actually a value !) to to a value within "Write" so I can compare it to numerical 0. I could also not find any debugging help.Ekki_bSun, 06 Jan 2019 14:59:07 +0100https://ask.libreoffice.org/en/question/178413/Sum empty cell returns #value (update)https://ask.libreoffice.org/en/question/178080/sum-empty-cell-returns-value-update/Not sure if this has been ask before
I'm trying to have SUM of 3 components =SUM(H211-F211)*E211 when there's a component (square) that is empty it returns #value
How do I prevent this from happening?
Thank you for helping in advance....
Updated....
i tried =(H211-F211)*E211 instead of =sum(H211-F211)*E211
see attachment of error
I still get #value error when I use formula [C:\fakepath\value error Libreoffice 01-02-2019.JPG](/upfiles/15464740327500592.jpg)
///////////////////////////
updated
I found the solution .... by experimenting
=IFERROR(SUM(H211-F211)*E211,0)
this replaced #value! (error) with 0.00 which i neededJG101Thu, 03 Jan 2019 00:22:41 +0100https://ask.libreoffice.org/en/question/178080/How to return empty cell from function in calchttps://ask.libreoffice.org/en/question/33893/how-to-return-empty-cell-from-function-in-calc/I want get a completely empty cell at return without using quotes, i.e. not like at previous [question](http://ask.libreoffice.org/en/question/11556/how-to-return-nullempty-from-function-in-calc/)denis.polkovnikovWed, 14 May 2014 11:07:11 +0200https://ask.libreoffice.org/en/question/33893/LO Calc: How do I define "search in values" as standard search option?https://ask.libreoffice.org/en/question/162244/lo-calc-how-do-i-define-search-in-values-as-standard-search-option/This exact question has been asked but not answered here: https://ask.libreoffice.org/en/question/36629/can-i-set-search-in-option-to-values-permanently-in-calcs-search-toolbar/
Got the same problem and I can try to be more precise.
I have a huge calc file with several tables. One of the table functions as source table, the others refer to it creating values out of formulae, like article numbers and price calculations with factors.
The content of a given cell is thus a formula and the output a value.
However, using STRG+F to find an article number is not working.
Only after opening up the search dialog, then opening the advanced options, then selecting "values", then searching again is doing the trick.
Repeat the process for every freaking search.
Neither does it keep the setting for the next use nor did I find any possibility to define my search settings.
Is there any workaround or recommendation? MS Excel has the same problem, but you can change the settings in fewer clicks and it keeps the settings at least until you close the program.
Any help appreciated!TanlladwyrTue, 07 Aug 2018 13:57:43 +0200https://ask.libreoffice.org/en/question/162244/How to execute a macro on a cell value?https://ask.libreoffice.org/en/question/162421/how-to-execute-a-macro-on-a-cell-value/ I am trying to execute a macro with an "if statement" or cell value. So, for example, if the value of cell ((B4 > 10, "trigger macro", "value is less than 10.))
Any advice or tips most welcome.MoritZWed, 08 Aug 2018 21:47:37 +0200https://ask.libreoffice.org/en/question/162421/Import current BTC and ETH values to Libreoffice Calchttps://ask.libreoffice.org/en/question/139677/import-current-btc-and-eth-values-to-libreoffice-calc/I want to import current Bitcoin (BTC) and Ethereum (ETH) values into LibreOffice Calc. How can I do it, please?
My specific purpose is getting vaules from btcturk.com or any other exchange operating in TRY; however please feel free to answer this question if you have an answer for USD or EUR.dx486Sun, 03 Dec 2017 11:02:53 +0100https://ask.libreoffice.org/en/question/139677/How to get a cell to read as a numberhttps://ask.libreoffice.org/en/question/157183/how-to-get-a-cell-to-read-as-a-number/When I copy and paste my cell reads as #value. How to make my formula read as a number.
sabyWed, 06 Jun 2018 18:42:51 +0200https://ask.libreoffice.org/en/question/157183/Calc: Extract unique values and add the numbers next to ithttps://ask.libreoffice.org/en/question/154475/calc-extract-unique-values-and-add-the-numbers-next-to-it/Gosh, I dont know how to describe this seemingly simple problem I have. An example is so much easier.
Say I have two columns :
Apple 5
Orange 3
Apple 1
Now I want to automatically create two columns where one is all the unique value in column A and the other is the aggregate of all the numbers that corresponds to that value. So for the table above it would create :
Apple 6
Orange 3
I found out how to extract the unique values here : https://ask.libreoffice.org/en/question/101374/find-distinct-values-in-a-column-formula-for-calc/
but I don't know how to add the numbers next to them
There are hundreds of items and potentially more incoming. So I'm wondering if Libre can automate the taskvulanalaWed, 09 May 2018 19:56:36 +0200https://ask.libreoffice.org/en/question/154475/Is it possible to check/test a cell if its background is other than default?https://ask.libreoffice.org/en/question/152204/is-it-possible-to-checktest-a-cell-if-its-background-is-other-than-default/Hello :)
I think the question is self-explanatory.
Nevertheless I will try to explain myself better.
I am looking for a way to fill a cell's value if its background color (which is the same as the row's background color) has changed from default value into a specific palette color.
Is this possible?
How quick should it be?
Thanks in advance!mzcl-mnFri, 13 Apr 2018 15:56:08 +0200https://ask.libreoffice.org/en/question/152204/How to copy the values or cells from a matrix subrange in Calc?https://ask.libreoffice.org/en/question/151533/how-to-copy-the-values-or-cells-from-a-matrix-subrange-in-calc/In Calc, version 5.x, if you want to copy only a subrange of cells that are the result of a matrix formula, how to do that?
That is, for example, if you put into cells A1:A5 respectively the values 1 to 5, and to fill B1:B5 you put into B1 the matrix formula {=A1:A5+1} (ctrl+shift+enter). Then, you want to copy only the cells or values B2:B4, but, selecting B2:B4 the copy option isn't enabled, why? how to copy a subrange only of a matrix calculation?
Is properly a problem or design of Calc version 5.x? or is a configuration matter? Is there any way to do that selected subrange copy?
Thanks!SanchoFri, 06 Apr 2018 08:07:50 +0200https://ask.libreoffice.org/en/question/151533/problem with multiple if function and whole numbershttps://ask.libreoffice.org/en/question/109802/problem-with-multiple-if-function-and-whole-numbers/Hi! My first post here!
I'm creating a conversion tool which takes a code number (913, 858 or 471 for example) entered in one cell and converts that number to another code in an adjacent cell.
Example:
**Input Cell A1: 913**
**Output Cell B1: 3.1**
I've got about 50 input codes to deal with, so I opted to use a multiple IF function which I researched here and came up with something like this:
**Multiple IF in B Column:** =IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75)
However, since I needed to use SUM to tally the total, I used the VALUE function to convert the B Column output to numbers by doing this:
**Multiple IF in B Column with added VALUE:** =VALUE( IF(A1=615,1.85) & IF(A1=270,1.37) & IF(A1=405,0.94) & IF(A1=550,0.75) )
The problem is that if the converted number is a whole number **( IF(A1=333,6.00) )**, that output is very odd. Depending upon where in the code that whole number is, the output could be 60, 6000, or in one instance, it output 0000600.
My question is this: How do I overcome this issue with whole numbers within this function?
Thank you all!
PS. I'm only capable of the most basic coding chores, so hopefully your response will utilize my present code, but if there is an equally simple way to do this, I'm open to another method as well.ggatlantaSun, 09 Jul 2017 20:59:07 +0200https://ask.libreoffice.org/en/question/109802/Calc Charts : How can I assign 2 values for the same axis ?https://ask.libreoffice.org/en/question/109351/calc-charts-how-can-i-assign-2-values-for-the-same-axis/Hello,
apologies if this has been answered elsewhere, I couldn't find it. I'm making a chart which compares 2 data sets, one of which is entered in complete numbers and one of which is entered as multiples of K (so "24" = 24,000). Comparing the two is impossible as entered because the scale is completely distorted. A colleague was able to make a chart which gives 2 different values for the same axis, using Excel, but I don't have Excel and can't ask her to make the chart for me every time. Is there any way to do this in LibreOffice ?
Here is the chart made by the colleague to illustrate what I mean : on the left side is the scale for the complete numbers (blue line), on the right side is the scale for the multiples of K (grey line).
![example chart](http://i.imgur.com/v4kUP15.png "example chart")
zpFri, 07 Jul 2017 18:56:40 +0200https://ask.libreoffice.org/en/question/109351/Part of file name as a sql query value?https://ask.libreoffice.org/en/question/94638/part-of-file-name-as-a-sql-query-value/ I am trying to figure out how to use a saved file name as part of the sql query to compete the query and then run it.
I have an sql statement that I use for another program to autocad and it uses a "WHERE =" that gets the = part based off the first 7 characters of the saved autocad file name and it works perfectly. I would like to recreate something like this for libreoffice but I am unsure how to map it to the file name as I cant insert a field into the query.
My goal is to be able to have WHERE = "SAVED calc file name" or really the first 7 digits of the file name as mine is Always a 7 digit number to start that is directly related to a Jobno field in the database.
AmbientTue, 09 May 2017 21:01:49 +0200https://ask.libreoffice.org/en/question/94638/Allow Entry In Cell Based On Value In Another Cellhttps://ask.libreoffice.org/en/question/94433/allow-entry-in-cell-based-on-value-in-another-cell/ Kindly look at the table below.
Along A1, A2, A3,….., we have a list of fruits.
Along B1, B2, B3,……, we have a list of the quantities of each item.
Along C1, C2, C3,……, we have the corresponding calorific values of each fruit.
Along the subsequent columns, we enter the number of a particular fruit eaten that day.
![image description](http://s018.radikal.ru/i502/1705/9e/6c7ea4744693.jpg)
How can I ensure that the total number of fruits entered does not exceed the total quantity available, i.e., since we have 3 oranges, all of which have been consumed by Tuesday, if I try to enter an orange on Wednesday it, either, prevents me from entering any value in that cell, or warns me.
Also, how can I ensure that at the bottom, it automatically displays the sum of the calorific value of fruits consumed that day.sluggerSun, 07 May 2017 18:28:55 +0200https://ask.libreoffice.org/en/question/94433/How to apply a text formatted formula with new datahttps://ask.libreoffice.org/en/question/92416/how-to-apply-a-text-formatted-formula-with-new-data/Sorry, guys, I think this is a trivial question, but I couldn't find this answer in my researchs.
This question is a follow up of this one (just in case someone needs more details): https://ask.libreoffice.org/en/question/92397/calc-how-to-lookup-a-formula-instead-of-a-value/
I have two spreadsheets: one with rules and respective formulas; and the other with my choices of rules (a rule could not be chosen, or even be chosen more than once). The second spreadsheet has each rule attached to its respective formula retrieved by:
=FORMULA(INDIRECT("Sheet1.C"&MATCH(A1,Sheet1.$A$1:$A$999,1)))
So, I have in the second spreadsheet a "text formatted" formula.
Let's say that I have these:
**Sheet1.A1:** `ID1`
**Sheet1.B1:** `34`
**Sheet1.C1:** `"XX"`
**Sheet1.D1:** `Sheet1.$B1*C1`
**Sheet2.A1:** `ID1`
**Sheet2.C1:** `10`
**Sheet2.D1:** `'= Sheet1.$B1*C1` (result of the formula above)
How do I apply the formula in **Sheet2.D1** to **Sheet2.E1**, for example, to get the resulting `340`?iperettaMon, 10 Apr 2017 03:06:19 +0200https://ask.libreoffice.org/en/question/92416/Calc showing sometimes function, sometimes valuehttps://ask.libreoffice.org/en/question/87489/calc-showing-sometimes-function-sometimes-value/ I'm using Win10 and LibreOffice 5.3.0.3.
I'm trying to use the function IFS - let's say in this way: IFS(a1=1;"A";a1=2;"B") etc
Now, in some cells I do indeed get what I want: either A or B depending on the value of the cell A1. But sometimes, seemingly completely arbitrarily, I only see the function itself, not the result.
Tools/options/calc/view/formulas NOT on!!
I have tried to write the function directly as well as using the wizard, but what stays is the seemingly random behavior: some cells continue to show value, some the function. And the cell formatting is exactly the same (number or text, doesn't change anything by the way).
Feels like a Bug. Any comments?JackSFSun, 12 Feb 2017 14:55:05 +0100https://ask.libreoffice.org/en/question/87489/How to highlight highest and lowest values in LirbreOffice Calchttps://ask.libreoffice.org/en/question/23297/how-to-highlight-highest-and-lowest-values-in-lirbreoffice-calc/I am making a stat sheet for a Lan party I am having. I would like to highlight the highest and lowest values in each column. For example the three people with the most kills with have their kill count highlighted.
I have an example but do not know how to replicate it.
http://imgur.com/8vVTNvObirdboy272Sun, 29 Sep 2013 21:37:56 +0200https://ask.libreoffice.org/en/question/23297/How to copy paste without data (functions) changing : calchttps://ask.libreoffice.org/en/question/78873/how-to-copy-paste-without-data-functions-changing-calc/When I copy (Data:Column - Rows) and paste right below prior data why do all my functions change.
How do I prevent this from happening.
Some functions I'm able to put "$" and it will keep the function (command) but I cannot do this for all functions (=sum( ))
How do I copy / paste without any data changing within the copied cells when I paste to a new row.
Purpose: trying to see overall data and sorted data in the same document.
thank you in advance for any help anyone provides. JG101Thu, 06 Oct 2016 18:49:41 +0200https://ask.libreoffice.org/en/question/78873/Duplicate Cell value if another cell meets criteriahttps://ask.libreoffice.org/en/question/78317/duplicate-cell-value-if-another-cell-meets-criteria/ How do I Duplicate Cell value if another cell meets criteria
please see document with description
thank you in advance for anyone who helps with Answer(s)
[Cell Value duplicate if Cell equals 1 09-29-2016.ods](/upfiles/1475171557206289.ods)
JG101Thu, 29 Sep 2016 19:53:30 +0200https://ask.libreoffice.org/en/question/78317/reference sheet name from cell valuehttps://ask.libreoffice.org/en/question/77065/reference-sheet-name-from-cell-value/ Hello
There was a similar questions posted I think but it was so vague I really couldn't understand what was actually be asked or the answer provided. So please do excuse me if this has been answered already.
I want to use a cell value to reference a sheet.
Column “B” contains item_code that matches a sheet name for example TILKGW68
Price column for example contains =TILKGW68.F2.
Challenge: Instead of having to spell out each sheet name in the
Price column I would like to reference column B.
I have a simple example ODS file to attach but it seems I am too new and need 3 points before i can upload. Since I am new, I don't know what these points are or how to acquire them. Hopefully I have included enough detail above to explain my objective.
Thank youElsoSun, 11 Sep 2016 15:37:02 +0200https://ask.libreoffice.org/en/question/77065/calc: If a field contains data, copy the entire row to another worksheethttps://ask.libreoffice.org/en/question/75300/calc-if-a-field-contains-data-copy-the-entire-row-to-another-worksheet/Hi everyone.
First time poster. As a background, I have taken over a position in a new company and they use Libre Office. I have several spreadsheets that could do with some tidying up and I have been working through those. But I am stumped on one.
1. I have a file that has several worksheets.
2. Worksheet 1 has a heap of master data in it
3. There is a field on the master data worksheet that staff complete with a number, each week.
4. But there are a lot of rows that do not need that number inserted
5. At present the previous person in my role would sort the master sheet each week, based on that one field, then cut-n-paste what they needed into the second worksheet.
I was wondering if there is a formula/macro I could use that would automatically copy a row, with say data in a set cell, across to the other worksheet, but append each line to the next line on the new worsheet?
Eg.
Column 1 Column 2 Column 3 Column 4
item Red supplier 4
item Blue supplier
item Green supplier 1
All I want to achieve is : if there is a value in column 4, copy that entire row to the other worksheet. If the column 4 is blank, do not copy to other worksheet. Such that all rows with a value in column 4 are appended to the other worksheet.
Hope someone can assist, and thank you in advancericktasWed, 17 Aug 2016 03:21:30 +0200https://ask.libreoffice.org/en/question/75300/In Calc, how can I hide #VALUE! in a cell when printed?https://ask.libreoffice.org/en/question/74191/in-calc-how-can-i-hide-value-in-a-cell-when-printed/I can set a document to hide zero values, but if I have a formula that's missing a value, that cell always prints #VALUE! But can there be a way to not show that warning in print?Chris Y.Fri, 29 Jul 2016 01:00:43 +0200https://ask.libreoffice.org/en/question/74191/=Rank #value! (remove)https://ask.libreoffice.org/en/question/73680/rank-value-remove/original - but returns #value!
=RANK(I16,$I$16:$I$313,0)
I would like it to return a blank instead of #value! how do I fix this function.
=IFNA(RANK(I16,$I$16:$I$313,0),"")
thank you in advance for any help.JG101Thu, 21 Jul 2016 04:57:25 +0200https://ask.libreoffice.org/en/question/73680/How to make a list with values from A1, A13, A25, this is, from every "+12" cell?https://ask.libreoffice.org/en/question/56528/how-to-make-a-list-with-values-from-a1-a13-a25-this-is-from-every-12-cell/ Hello,
Using Libre Calc (Version: 4.4.5.2).
I'd like to build a list (column) containing values from A1, A13, A25, this is every 12 cell bellow. Would anyone advise about any function to this?
Thanks,
AS
[Edit: added lo4 tag]ajrsantosFri, 21 Aug 2015 10:46:08 +0200https://ask.libreoffice.org/en/question/56528/Get sheet names as a listhttps://ask.libreoffice.org/en/question/40885/get-sheet-names-as-a-list/Hey guys,
i am kinda stuck right now,... I got a workbook with lets say 10 sheets. These sheets have other names than sheet1, something like abc, hjk, wer etc..
And in my last sheet, i want to have a list of all sheets but the last 3, so i want to list the names of sheet 1 to 6. I also want to get values from these sheets in my summary, fe =abc.$M$59, =hjk.$M$59, wer.$M$59. (Which is the same cell in every sheet 1-6)
The reason why I don't want to do this by hand is that I am going to add sheets over time (between 1 and 6, while the last 3 will always stay the same) That's why I want them to be added automatically, the name and the value.
I hope you guys can understand what I am trying to do and can help me.
regards JavealThu, 09 Oct 2014 14:07:47 +0200https://ask.libreoffice.org/en/question/40885/