Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 01 Oct 2019 18:22:07 +0200Calc row/column header shadinghttps://ask.libreoffice.org/en/question/61022/calc-rowcolumn-header-shading/ Hi there,
The row headers "1,2,3..." and column headers "A,B,C..." have a shading effect... half dark grey, half light grey.
Or for selected rows and columns... half dark blue and half light blue.
How can I have single-tone row/column header bars?
I find this semi-3d effect distracting, I would prefer my menus to be as simple and unintrusive as possible.
Many Thanks
Ben
bgaskinWed, 18 Nov 2015 01:39:27 +0100https://ask.libreoffice.org/en/question/61022/Swap unique cell data with rows?https://ask.libreoffice.org/en/question/210895/swap-unique-cell-data-with-rows/Someone gives me a spreadsheet of a schedule. The columns are the days of the month, and the rows are the workers names. The cells hold the names of the places they are scheduled. Two people cannot be scheduled at the same place on the same day.
It might looks like this:
![image description](/upfiles/15699466675171223.png)
I would like to convert the spreadsheet so that the rows are the places, and the cells hold the peoples names. I would like the output to look like this:
![image description](/upfiles/1569946749688743.png)
I could do this manually but was wondering if there was some functionality in Calc that would make automating this process easier?
I have tried writing a formula to fill in the first cell for the 'Bathroom' row in my desired solution:
> =INDIRECT(ADDRESS(MATCH($A$11,B3:B6,0)+2,1))
Is there a simpler way?SS_HELPTue, 01 Oct 2019 18:22:07 +0200https://ask.libreoffice.org/en/question/210895/drop down list to fill other cells in rowhttps://ask.libreoffice.org/en/question/208816/drop-down-list-to-fill-other-cells-in-row/I just added drop down lists on my inventory spreadsheet.
I'm using the drop downs for the 'item type' column (column A), with the source being an item list on a different sheet within the spreadsheet.
column B is for the dimensions, column C for weight and a few more detail columns.
is there a way to automatically have the other fields filled in with the details once I select the item type from the list?
thank youclos911Mon, 16 Sep 2019 04:22:42 +0200https://ask.libreoffice.org/en/question/208816/Calc alternating row colors? LO 6.0.4.2https://ask.libreoffice.org/en/question/208253/calc-alternating-row-colors-lo-6042/Hi
I can not make the rows alternate with different colors?
Tryed to search but nothing works
How do i make the rows have f ex white and grey alternating?
Thanks J
![image description](/upfiles/15682799686725351.png)JoelHWed, 11 Sep 2019 16:05:47 +0200https://ask.libreoffice.org/en/question/208253/[calc] alternating color for row pairshttps://ask.libreoffice.org/en/question/206236/calc-alternating-color-for-row-pairs/ Hi everyone,
I'd like to add alternating colors to my spreadsheets. However, not every single row, but every pair of rows. e.g. rows 1+2 grey, 3+4 white, 5+6 grey, ...
Thanks in advance!
questionaireTue, 27 Aug 2019 10:52:05 +0200https://ask.libreoffice.org/en/question/206236/How to sort columns in rowshttps://ask.libreoffice.org/en/question/124573/how-to-sort-columns-in-rows/I've a block of columns and rows that are similar to this:
- 1 |8 |38|47|43
- 21|32|3 |37|26
- 23|12|33|65|51
and I want them to sort into this:
- 1 |8 |38|43|47
- 3 |21|26|32|37
- 12|23|33|51|65
IOW, I want the columns in each row to reorder such that the number values increase to the right. I select a block of cells...Sort...enable the Left to Right (Sort Columns) feature...OK, but it only works on one row at a time, even if I have multiple rows selected in the block. What am I doing wrong?
LibreOffice 5.3 with latest updates
TIAseschenburgSun, 20 Aug 2017 05:33:03 +0200https://ask.libreoffice.org/en/question/124573/bookmarking a row in calc?https://ask.libreoffice.org/en/question/202889/bookmarking-a-row-in-calc/my calc file has 7214 rows...
1 row for each individual items in an inventory.. I know it sounds messy but I have it organized so that identical item types are next to each other going down the rows..
I would like to know if there is a way to quickly get to a row in a spreadsheet that doesnt involve scrolling endlessly?
also i can't rely on referencing the row's number, i add rows in several locations..
what i currently do is; I use auto filter to select the item type I'm looking for in a column, I note the row number of the item type, then scroll down the sheet to said row.. i know there's got to be a more efficient way...?
clos911Wed, 31 Jul 2019 05:28:30 +0200https://ask.libreoffice.org/en/question/202889/Select last cells with a value for a given rowhttps://ask.libreoffice.org/en/question/198930/select-last-cells-with-a-value-for-a-given-row/Hello all,
My very first post. happy days.
I am trying to select the last 5 cells that contain a value (number) from a row (very long row with lots of gaps).
Ideally I would like to get the last 5 values for a number of rows from sheet 1 and have those end up in sheet 2 (a lovely summary with the last 5 results).
I can update manually but if I could automate this so that values are sent to sheet 2 as I update sheet 1 that would be fantastic.
thank you.sr1pulpoTue, 25 Jun 2019 13:22:17 +0200https://ask.libreoffice.org/en/question/198930/How can I select non-consecutive rows in Calc?https://ask.libreoffice.org/en/question/191364/how-can-i-select-non-consecutive-rows-in-calc/I need to move non-consecutive rows in Calc. How do select a group of these rows and then move them, delete them, copy them,...?kingwillardWed, 24 Apr 2019 10:27:19 +0200https://ask.libreoffice.org/en/question/191364/how to format all rows & cells in a filehttps://ask.libreoffice.org/en/question/190301/how-to-format-all-rows-cells-in-a-file/ I'd like to change the default text font and size as well. befuddledSat, 13 Apr 2019 16:31:34 +0200https://ask.libreoffice.org/en/question/190301/Is there a way to prevent Calc from showing hidden rows and columns when copying sheets?https://ask.libreoffice.org/en/question/187946/is-there-a-way-to-prevent-calc-from-showing-hidden-rows-and-columns-when-copying-sheets/MS Office convert here and overall very impressed with how far the LibreOffice suite has come. However one issue has us very frustrated at the moment.
We have a spreadsheet that contains a dozen hidden rows and a few hidden columns, with some behind-the-scenes formulas and values that we rarely need to see. We routinely create copies of the previous period's sheet within the same workbook and make changes to the copy. When we did this in Excel the hidden rows and columns stayed hidden. However, Calc shows all the hidden rows and columns in the newly pasted sheet, requiring us to go in and tediously re-hide each of them whenever we make a copy.
We have tried messing with the Paste Special options, but to no avail. Hoping there is a workaround we haven't noticed or thought of.
If not, modifying Calc's copy-paste code to maintain hidden rows and columns would be a nice thing for the developers to work on.
Thanks!
Jeff_SSat, 23 Mar 2019 02:04:44 +0100https://ask.libreoffice.org/en/question/187946/Counting duplicate numbers in various rows on a sheethttps://ask.libreoffice.org/en/question/186143/counting-duplicate-numbers-in-various-rows-on-a-sheet/Hello. This question has been answered in various ways, but not in ways that suit my application. An example screen shot is attached. I want to count duplicate occurrences of any set of numbers row-by-row on a sheet. The formula in a separate column ("Duplicates") is highlighted if it exceeds three numbers duplicated in any other row, as shown on rows 4 and 11. The raw data columns/rows don't need to be highlighted; I just did that for clarity. I will highlight the duplicates column with a rule; all I need is a formula for the column. Thanks huge for any help provided! [C:\fakepath\LibreOffice Question.png](/upfiles/15521611141100112.png)Larry in WaterlooSat, 09 Mar 2019 21:05:01 +0100https://ask.libreoffice.org/en/question/186143/Partial sums of a column of valueshttps://ask.libreoffice.org/en/question/184266/partial-sums-of-a-column-of-values/ I have a column of values like this:<br />
0`<br />
1<br />
3<br />
0<br />
0<br />
12<br />
45<br />
56<br />
0<br />
How do I compute the subtotals of contiguous nonzero numbers?Ramil123AbcSat, 23 Feb 2019 01:53:51 +0100https://ask.libreoffice.org/en/question/184266/How to designate a row as a heading-rowhttps://ask.libreoffice.org/en/question/183230/how-to-designate-a-row-as-a-heading-row/ how to ...EagleturtleThu, 14 Feb 2019 21:19:21 +0100https://ask.libreoffice.org/en/question/183230/"Float" the top row in Calc to each page as you scroll downhttps://ask.libreoffice.org/en/question/180882/float-the-top-row-in-calc-to-each-page-as-you-scroll-down/Is there a way to do this? I use the two rows for column headers, probably like most folks, but when the number of rows is longer than my screen I would like to keep the top row on the screen as the next screen scrolls up. Clear as mud? Appreciate any help. mithmcMon, 28 Jan 2019 18:36:36 +0100https://ask.libreoffice.org/en/question/180882/How do I compile a 6000 line sales record into a brief product report in Calc?https://ask.libreoffice.org/en/question/179038/how-do-i-compile-a-6000-line-sales-record-into-a-brief-product-report-in-calc/ I need advice on the use of LibreOffice calc.
I have a year end report of all of my individual online sales for the past year; individual rows for each sale. Cells containing the name of the product sold, number of units sold, and amount paid by the buyer.
There are over 6000 rows (sales), and there are over 1900 different products.
What I want to do is consolidate the rows into a single row for each different product by name, so that there is a cell with the product name, a cell with the total number of units sold, and a row with the sum total paid for all units sold.
I am self taught in the use of spreadsheets, and the simple solution to this problem is not apparent to me. I could sort my product name and add up the totals for each different product, but that could take me days, and I know that there has to be an easier way.
Can anyone help?RJBowmanFri, 11 Jan 2019 23:18:24 +0100https://ask.libreoffice.org/en/question/179038/Copying rows by dragging to the righthttps://ask.libreoffice.org/en/question/176850/copying-rows-by-dragging-to-the-right/Hello,
quick question: how i can copy contents from, for example A5 to D5, without using =A5 formula in D5?
What function should i use in D5, to be able to ctrl-drag that cell to the right, so it would fill cells to the right with correct values (E5=222, F5=333, G5=444, G=555.. and so on?)
screen:
![image description](/upfiles/15459404529998944.jpg)
Thanks for help!ch0senThu, 20 Dec 2018 14:50:42 +0100https://ask.libreoffice.org/en/question/176850/Adding Blank Rows for Sequential Missing Numbershttps://ask.libreoffice.org/en/question/167356/adding-blank-rows-for-sequential-missing-numbers/ I have a large database import I am working with that is several tables of data that have to be combined into one master spreadsheet(csv) for import.
There is a primary key with both tables but both tables are missing numbers say the order is 11-83529 but neither table is completely sequential.
The only solution I have found the works on that column is to fill - series choosing linear and using the line numbers.
However I need to act that way on the whole , adding a row each time it finds a missing number and adding the number to the first column? Does anyone know how to do this?
chrchcolTue, 02 Oct 2018 04:37:42 +0200https://ask.libreoffice.org/en/question/167356/Calc Rows between matching entrieshttps://ask.libreoffice.org/en/question/162212/calc-rows-between-matching-entries/Hey everyone, I'm new here so just a short Hello and hoping you're all well.
I have a one column list of over 3000 rolls of a dice. E.g (shows it as a row here, but it is a column).
3
6
5
3
2
3
4
4
2
2
3
etc, etc
I need a way to know how many rolls before the same number came up again. So, in the above snippet of the data, if I checked #3 it would return a result of: 3, 2, 5 (from the 1st #3 to the 2nd was 3 rolls, from the 2nd to 3rd was 2 rolls and from the 3rd to the 4th was 5 rolls). I could of course count all this manually but going through a data set of 3000 figures 6 separate times would be very time consuming and just one error could throw the statistics off. Rows between each matching item would also work, I'd just have to add +1 to each value returned.
Can anyone help me with a formula that could do this for me?
Many Thanks in advance, Shane.
ShaneLeveneTue, 07 Aug 2018 08:22:30 +0200https://ask.libreoffice.org/en/question/162212/Odd Insert Row problemhttps://ask.libreoffice.org/en/question/156333/odd-insert-row-problem/ Have a spreadsheet that I'm filling out data, and pictures in - but no formulas. When I go to add an extra row between two filled out rows, only the first box moves. This happens if the extra row is added above, or below the filled out row.
If the row is empty, insert row works as expected. I can't copy paste rows because of this.
Not seeing anything in help about any sort of insert configurations, or something that I ought to be doing different.
This really is hampering my ability to edit the spreadsheet.
Currently running 6.04.2 (x64)tmnatheTue, 29 May 2018 01:14:40 +0200https://ask.libreoffice.org/en/question/156333/New to Calc, I want to limit the number of rows and columns visible to those I actually needhttps://ask.libreoffice.org/en/question/156293/new-to-calc-i-want-to-limit-the-number-of-rows-and-columns-visible-to-those-i-actually-need/ The rest are entirely redundant but always always there, it's most annoying.
Many thankstswMon, 28 May 2018 17:53:47 +0200https://ask.libreoffice.org/en/question/156293/How do I exclude rows from sort function?https://ask.libreoffice.org/en/question/155973/how-do-i-exclude-rows-from-sort-function/ I use Calc to create task spreadsheets.
I want to be able to select all --> Data --> sort by 'priority' (for example) without sorting my headings or notes I've left.
These headings are contained to certain rows, so I think it should be possible to leave them out of the sort.
The easy workaround is just manually selecting everything except what I'm trying to leave out, but I'd like a more elegant solution.
Thank you!
hawthorneFri, 25 May 2018 00:31:30 +0200https://ask.libreoffice.org/en/question/155973/How to freeze the first row in Calchttps://ask.libreoffice.org/en/question/150748/how-to-freeze-the-first-row-in-calc/Forget it, I'm an idiot. I gave up smoking two and a half days ago. Head needs a bit of time to recover. Sorry.freezerowsWed, 28 Mar 2018 14:12:39 +0200https://ask.libreoffice.org/en/question/150748/Rows not movinghttps://ask.libreoffice.org/en/question/150301/rows-not-moving/FIXED-sorta
I have a macro that reads a UPC code from a barcode reader. I have set up a listener on the cell, and receiving the data, I use the macro to look up the UPC code to get the price and description. I then place the price and description on the same worksheet as the listener cell by row. That seems to work ok, but when I enter more rows than what is showing, the cells do not move up. How can I always show the last row - or make the rows move up so the last row is visible?
I have a frozen area at the top (rows 1-11) which contains buttons and information necessary to process the list of items. The inserted rows start at row 12 and go down to as many inputs as are necessary, but only the first 23 show! The user can use the slider bar to see the items pass row 23, but is there some method to show the last row?
I tried ThisComponent.getCurrentController().setFirstVisibleRow(rowx), where rowx was the last row, but that did not work. Version 5.2.6.2 running on Anti-X 16.
Ok, I fixed this by placing my listener below the frozen row. Apparently, I have to give the focus to the cell where the listener is and when I do that, the whole window is re-written which means that even if I was able to move the row down, the re-writing of the window sets it back to where it was.
This is a Klunky fix, and instead of one cell as a listener, I am now using every row (in one column) as a listener. It works, but not as clean as having just one cell above the frozen rows.wsxianFri, 23 Mar 2018 23:31:35 +0100https://ask.libreoffice.org/en/question/150301/How do I change the minimum number of rows that can be displayed in Calc?https://ask.libreoffice.org/en/question/139195/how-do-i-change-the-minimum-number-of-rows-that-can-be-displayed-in-calc/ Right now I seem to be limited to about 20 rows as a minimum.HyphenMon, 27 Nov 2017 17:57:47 +0100https://ask.libreoffice.org/en/question/139195/select and Delete many rows with shortcuthttps://ask.libreoffice.org/en/question/129884/select-and-delete-many-rows-with-shortcut/ When i use a filter take for answer many rows (3000)
i want select this rows and delete them .
the only way i know is drag mouse to the end row
Have any other way with shortcuts to select this rows?dimzevSat, 09 Sep 2017 18:15:07 +0200https://ask.libreoffice.org/en/question/129884/looking for last row used programmaticallyhttps://ask.libreoffice.org/en/question/103023/looking-for-last-row-used-programmatically/Hello,
I've been searching for a property to show the number of rows and columns used. I want to loop through data on the sheet, but not the entire sheet itself. I also want to append data at the end of a sheet so knowing where I can safely start inserting data is important.
I've tried the code below but it gives me the grand total.
msgbox thiscomponent.sheets(0).rows.count
I have found some solutions but they are over 3 years old, I was hoping Libre office had moved on and maybe this is now available without moving the cursor and querying it's position.
The excel vba equivalent would be:
UsedRange.rows.count
EryanTue, 27 Jun 2017 17:08:54 +0200https://ask.libreoffice.org/en/question/103023/hide row based on passed datehttps://ask.libreoffice.org/en/question/94826/hide-row-based-on-passed-date/My first column is date, and then there are 12 more columns for different information.
Every row starts with a different date, followed by this other information.
When the date is passed, I want the hole row (with passed date) to "disappear".
- Manually it is easy, just select row range, right click, select hide row.
How do I make this automatically, when starting the file another day?
___________________________________________________
Version: 5.2.6.2
Build ID: a3100ed2409ebf1c212f5048fbe377c281438fdc
CPU-treads: 4; Version OS: Windows 6.2; UI-rendering: standard;
Language: sv-SE (sv_SE); Calc: groupstipetThu, 11 May 2017 17:18:25 +0200https://ask.libreoffice.org/en/question/94826/Switch rows? (Calc)https://ask.libreoffice.org/en/question/91840/switch-rows-calc/If I have two rows in calc (say, rows 5 and 15) that I want to switch around (the information in 5 is moved to 15 and vice-versa) is there any function in libreoffice that allows this?
I am doing a lot of this kind of switching in my documents.
What I am currently having to do to make them switch places, using rows 5 and 15 as an example is:
1) cut/paste the info in row 5 into an empty row, then:
2) cut/paste in info in row 15 into row 5, then
3) Cut/paste the info in row 5 into row 15
I know it doesn't sound like too much hassle, but after doing this many times over many different documents the time it's taking is starting to add up!appreciatethehelpSat, 01 Apr 2017 04:53:10 +0200https://ask.libreoffice.org/en/question/91840/Assume data in column A repeats the row number. Column B contains random data. What formula (in column C beyond row 10) would yield the average of the last 10 values in column B?https://ask.libreoffice.org/en/question/90462/assume-data-in-column-a-repeats-the-row-number-column-b-contains-random-data-what-formula-in-column-c-beyond-row-10-would-yield-the-average-of-the/ Spread sheet
aceking370Fri, 17 Mar 2017 20:55:39 +0100https://ask.libreoffice.org/en/question/90462/