Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenThu, 23 Jul 2020 21:28:07 +0200best method for displaying field data from one sheet to another in calchttps://ask.libreoffice.org/en/question/256758/best-method-for-displaying-field-data-from-one-sheet-to-another-in-calc/I have many .ods spreadsheets with data. On the spreadsheets the data fields are on row 1, each field has its own column, I have 93 fields, so 93 columns for data. The data starts on row 2 with, the data in each field being in the appropriate column, there are many rows of data.
I only need the data from 7 of the 93 fields. What I do currently do to view the data I need is I create a second sheet, on row 1 of the second sheet I fill in the field names that I want in columns A thru G, then starting on row 2 I put in simple formulas like this into the columns:
=$Sheet1.C2, =$Sheet1.G2, =$Sheet1.L2, =$Sheet1.U2, =$Sheet1.Y2, =$Sheet1.AF2, =$Sheet1.AO2
I then paste the formula down the columns. This lets me see only the data of the fields I want. To try and simplify this, I saved this spreadsheet with my 2nd sheet formulas as a blank template where I would paste all the data into sheet1 and sheet2 would act as a filter to show me the data. The only problem is the data speadsheets that I have do not have the same order of the data fields names, they do not display in the same columns, they are in rather random columns for each spreadsheet, but the fields names are the same and are found in all my spreadsheets they are just not in the same location, which leads me to track down the column of the field I want on sheet1 and edit the formulas in sheet2 to display the appropriate data. I tried a pivot table but it displayed my data in alphebetical order, the order of the fields in my sheet2 have to be the specific order I have in the row 1 data fields.
Looking for recommendations to simplify my process, any help is appreciated. I'm on windows 10, LO version 6.4.4.2 x64.clos911Thu, 23 Jul 2020 21:28:07 +0200https://ask.libreoffice.org/en/question/256758/is there a way to store a formula and "paste" it on command?https://ask.libreoffice.org/en/question/227407/is-there-a-way-to-store-a-formula-and-paste-it-on-command/I have a formula that I use on certain cells in the M column. My method is first I copy a cell that contains the formula, then I do a special paste of 'formula only' into the new cell, now the cell has the formula which is what is desired, works fine but.. This process can be time consuming as I have to find a cell in the column and paste formula only into the cell I decide on.
Is it possible to have a more efficient method to apply the formula onto a cell? I tired to keep the text of the formula on a separate sheet but pasting just the text does not keep the row numbers in sequence as with the $ sign..
Thank you.
here's a copy of the formula I'd like to readily apply to cells for context
=IF(ISNA(VLOOKUP(A4,'Sheet1'.$A$3:$M$1358,MATCH($M$2,$'Sheet1'.$A$2:$'Sheet1'.$M$2,0),0)),"",VLOOKUP(A4,'Sheet1'.$A$3:$M$1358,MATCH($M$2,$'Sheet1'.$A$2:$'Sheet1'.$M$2,0),0))clos911Fri, 31 Jan 2020 20:47:57 +0100https://ask.libreoffice.org/en/question/227407/is there a way to select the "Block Margins" of vlookup results? (calc)https://ask.libreoffice.org/en/question/226748/is-there-a-way-to-select-the-block-margins-of-vlookup-results-calc/I have a sheet with formulas for vlookup.
I use keyboard shortcuts to "Select to Lower Block Margin" and "Select to Right Block Margin".
I use those two shortcuts very often on cells with text and cells with numbers. I would like to be able to use the same shortcuts on formula results.. when I use the shortcut "Select to Lower Block Margin" on the cells with the formula it will selects all the way to the bottom row 1048576..
any help is appreciatedclos911Mon, 27 Jan 2020 03:47:46 +0100https://ask.libreoffice.org/en/question/226748/Please allow shorthand referencing of entire columns in calc, eg =sum(a:a)https://ask.libreoffice.org/en/question/20913/please-allow-shorthand-referencing-of-entire-columns-in-calc-eg-sumaa/I think this would be a real usability boostcalcmanWed, 31 Jul 2013 10:27:48 +0200https://ask.libreoffice.org/en/question/20913/automatic formula when add row?https://ask.libreoffice.org/en/question/17859/automatic-formula-when-add-row/I have a little problem with Calc. What I want is when I add a row, the formula from previous row is automatically applied to the new row.
Let say that I have three rows and three columns. In the third column, I enter formula: =A+B. And then copy that formula for the rest two C column. In the fourth row, I put a total from all numbers in column C. Now I want to add a row on the top of the fourth column, and I want formula for column C (=A+B) automatically applied.
In excel this can be easily done by add an additional row between the total row and those rows above it. And then I just need to add another additional row on top of the total row, and then put numbers on the first empty rows for column A and B, and column C will automatically filled. But it seems that I can't to this in Calc.
How can I solve this problem?
Thanks. uti_nuncWed, 22 May 2013 11:10:09 +0200https://ask.libreoffice.org/en/question/17859/