Re: [libreoffice-users] calc
Be sure to include the *$* symbol in the Array. Remember: the Index, *2*, represents the (second) column, which is referenced to the leftmost column within the (locked) Array, *$A$2:$B$4*. NOTE: The data in column *A *need not be only a numeric value; the data may be text also. Op 7 jun. 2024, om 19:11 heeft Ekerette Ekpo het volgende geschreven: How did you do it with vlookup? I tried it out and ended up with a #N/A in cell B4. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] LO Draw Newbie
Hi: I am using Windows10x64bit Home on DELL desktop and LO 7.3.2.2 (x64) / LibreOffice Community. I have never made any use of the LO Draw module in the past, and wonder if it is suitable for creating a 2-dimension drawing that shows room layout with dimensions, to scale if possible. My immediate need is to create a document/drawing of the household room dimensions, e.g., square footage dimensions, as if I needed to paint the ceiling or the walls or needed to lay down carpeting or hard wood flooring, or calculate/show square footage of lawn areas, etc. Is LO Draw the appropriate module for me to learn? Or, is there some other program that is simpler to learn and to use? Up till now I have been making a hand sketch of the area of interest, scan the sketched layout, create a PDF and then type/enter dimensions as text annotations within the PDF. I think there might be a simpler method. I look forward to receiving suggestions/comments from the LO Draw community users. In the meantime, I will be reading the LO Draw Users Guide. Regards, VinceB. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Re: Removal of Previously Marked Page Breaks
I have found a solution to my problem. To reset/remove all existing manual page breaks within a .ods file: * use View > Page Break, * hover mouse cursor somewhere within the displayed Page 1 area, * right click on the mouse, * select Delete Page Breaks. This effectively resets the .ods file to default without manual manual page breaks. The print output will flow onto pages that are consistent in area size, which is what I needed. I must remember in the future that there is no button involved, but rather a requirement of mouse pointer position and a right-click on the mouse device. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Removal of Previously Marked Page Breaks
Hi Harald: I have viewed your reference URL information and found it to be very informative. Thank you for authoring that information and making it available. I used the English version; here, I am in the USA. I downloaded both .ods files and followed along as best that I can. Most of my tables are wider than in height, so I normally do set page for Landscape orientation and to Letter rather than to A4. VinceB. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Removal of Previously Marked Page Breaks
Thank you Harald for your reply and info. I shall have a look at it soon. Regards, VinceB. On 1/20/2023 5:24 PM, lo.harald.ber...@t-online.de wrote: Can that help? https://wiki.documentfoundation.org/Videos/Page_and_printer_setup_in_Calc Regards Harald -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Re: Removal of Previously Marked Page Breaks
Edited: On 1/20/2023 3:33 PM, Vince@Verizon wrote: LibreOffice 7.3.2.2 (x64) Calc spreadsheet on Win10 desktop -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Removal of Previously Marked Page Breaks
LibreOffice 7.3.2.2 (x64) on Win10 desktop __ Once again I am having difficulty in arranging balanced page breaks. A past experience was solved by removing the existing page breaks. However, I cannot locate the control button for eliminating the previously marked page breaks. Please, let me know how to default the page breaks. TNX. Regards, VinceB. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Anchoring of Internet Hyperlink
Version: 7.3.2.2 (x64) / LibreOffice Community
Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster;
VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL
Hi:
I had set two Internet button hyperlinks {in cell P14 and cell P17} of a
Calc sheet. I changed the width of column [K] by selecting Optimum
Width and adding + 0.1" width.
Now, after changing the [K] column width, the two previously set
hyperlinks are displayed/overlapping cells to the left of where I
expected them to be located, {specifically overlapping cells N14 and
N17}. Why?
It seems that the hyperlink is not anchored to the cell in which it was
originally set. How must button type hyperlinks be anchored?
TIA.
VinceB.
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Using A:A in LO Calc
Version: 7.3.2.2 (x64) / LibreOffice Community Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0 CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL ___ Hi: Somewhere within Calc 7.3 there is a setting for allowing a full column range reference, e.g., [A:A], to be entered within a calc formula. I believe this functionality was added in Calc 5.0. I don't know if it is a global effect or is implemented on an individual spreadsheet basis. I have used it once before, but am unable to locate it now. I seem to recall that there are three options available, including: 1. RxC 2. Excel format 3. ? can't recall, perhaps scientific notation? Please help. TNX for reading about my problem. Regards, VinceB. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Re: Explanation of Formula that was used in Excel
On 10/30/2022 12:37 PM, Vince@Verizon wrote: Hi: Version: 7.3.2.2 (x64) / LibreOffice Community Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0 CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL I am attempting to understand a formula that was suggested for use within MSExcel2016 so I might us it within LibreOffice Calc. The formula is: =INDEX(IF(INDEX(A:A,LARGE(IF($A$4:$B$43=$G$2,ROW($A$4:$A$43),""),ROW(1:1)))=$G$2,E:E,F:F),LARGE(IF($A$4:$B43=$G$2,ROW($A$4:$A$43),""),ROW(1:1))). When I copy/paste this formula first into Notepad and then copy/paste from Notepad into a LO Calc spreadsheet, a #VALUE! error is returned. I have not ever used e.g., INDEX(A:A) nor ROW(1:1) as a range identifier/specifier within a formula, so I wonder if they are acceptable within LO Calc, or is that causing the error, #VALUE! ? I believe those identify/specify the entire [A] column and the entire [1] row; is there an equivalent within LO Calc. Once the error is cleared then I will continue to attempt to understand what the formula is calculating/doing within LO Calc. I can see that it is evaluating the data within cell $G$2, the LARGEST value within A:A, within E:E, and within F:F; but not much else is making any sense for me to understand. A simplified example would be appreciated here. Thanks for reading about my problem and for your comments/reply. VinceB. Update: Here is a link to the original MSExcel spreadsheet, the OP posted at the Excel Forum; the OP was seeking "Best formula to lookup most recent and second most recent result from list of data" on 01-04-2016, 02:50 AM: https://www.excelforum.com/attachments/excel-general/438242d1451890188-best-formula-to-lookup-most-recent-and-second-most-recent-result-from-list-of-data-lookup-formula.xlsx __ Unfortunately, I do not understand the significance of: ROW(1:1) within the original formula. Error #519 says in part, The formula yields a value that does not correspond to the definition; Or a cell that is referenced in the formula contains text instead of a number. No result (#VALUE is in the cell rather than Err:519!) and it is true that columns H:J for row1 contain text strings, not numerical data. I am still at a loss of understanding . -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Explanation of Formula that was used in Excel
Hi: Version: 7.3.2.2 (x64) / LibreOffice Community Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0 CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL I am attempting to understand a formula that was suggested for use within MSExcel2016 so I might us it within LibreOffice Calc. The formula is: =INDEX(IF(INDEX(A:A,LARGE(IF($A$4:$B$43=$G$2,ROW($A$4:$A$43),""),ROW(1:1)))=$G$2,E:E,F:F),LARGE(IF($A$4:$B43=$G$2,ROW($A$4:$A$43),""),ROW(1:1))). When I copy/paste this formula first into Notepad and then copy/paste from Notepad into a LO Calc spreadsheet, a #VALUE! error is returned. I have not ever used e.g., INDEX(A:A) nor ROW(1:1) as a range identifier/specifier within a formula, so I wonder if they are acceptable within LO Calc, or is that causing the error, #VALUE! ? I believe those identify/specify the entire [A] column and the entire [1] row; is there an equivalent within LO Calc. Once the error is cleared then I will continue to attempt to understand what the formula is calculating/doing within LO Calc. I can see that it is evaluating the data within cell $G$2, the LARGEST value within A:A, within E:E, and within F:F; but not much else is making any sense for me to understand. A simplified example would be appreciated here. Thanks for reading about my problem and for your comments/reply. VinceB. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Editing/Replacement of existing formulae
Version: 7.3.2.2 (x64) / LibreOffice Community Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0 CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL Hi: I have several tables/datasets within a Calc spreadsheet that uses two Name Box names: 1. RNG_YTD2022 2. Career_RNG which seem to be working properly. I am having difficulties and fatigue issues attempting to replace, for example, =SUMPRODUCT(RNG_YTD2022>180,RNG_YTD2022<189) , located within a column/cell with =SUMPRODUCT(Career_RNG>180,Career_RNG<189) , located within the same column/cell. (BTW: The numeric values 180 and 189 change from within one row to another row; I wonder also if I should learn about using the FREQUENCY() function?? ) I made an attempt to display formulae within the spreadsheet cells and make use of Find and Replace, w/o success. I would greatly appreciate suggestions/hints on achieving the replacement/exchange of the above formulae. TNX for reading this. VinceB. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Highlight & Delete Column Data to End of Calc Sheet
Hi: LibreOffice 7.3.2.2 on Win10 build 19043 on desktop PC. How should I highlight all rows above row 600 so I could then remove the unwanted __ entries from my spreadsheet? When working on a Calc sheet, I copied a drop down list, i.e, _2 Category>_, to the end of column [AC]. Still not sure how to correctly do that! The way I did it was to first highlight the column's heading/label_,_ _[_AC_]_; doing that highlighted the entire column. Then, I de-selected the heading/label... I think there is a shortcut key combination to accomplish this, but unsure. (There is no data within columns above row 600 other than within column [AC].) Now, I want to remove all entries that are located above row 600. I used Ctrl-End, which got me /there/, but I need to highlight all rows above row 600 - to the very last row, and then "Clear All" the data contained within cells that are above row 600, somehow Please help. TNX. VinceB. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Updating Data in Calc
Hi Hylton: Thank you, OM, for giving this problem some thought; I do appreciate your suggestion/comments. I understand your approach in your suggested solution; however, it does not satisfy my requirements. The numerical data that is entered, or calculated in this situation, within the rows of column [$M] must be reflected, overtime, only at cell $Y$4. (I may later need to assign a name for cell Y4; something like: /Predicted Remaining Fuel (Gals)/ for use in other, future, calculations). Perhaps I should have written in my OP that "The formula /presently/ in $Y$4=$M18-$W$4". I expect that that formula, obviously,must be changed to reach a solution to my problem, but "=SUM(M18-$W$4)" does not do it for me. Please note that the updated/reflected numerical data must be displayed only within cell Y4, and not elsewhere. Sorry for any confusion that I may have caused. I have other Calc spreadsheets that require this same type of numerical data updating. In 2020 Jan/Feb, I received a workable solution to one of my (bowling scores) spreadsheets; IIRC, it invoked the =INDEX( ) function, i.e., /=INDEX($'2020-21 WINTER SR LEAGUE'.G6:$'2020-21 WINTER SR LEAGUE'.G41,COUNTA($'2020-21 WINTER SR LEAGUE'.G6:$'2020-21 WINTER SR LEAGUE'.G41),1)/, but that has somehow gotten messed up by me and no longer works (another story, which I may soon write about in another thread). I mention =INDEX( ) here because I feel that function, which I still do not understand, may be needed to solve my Y4 updating problem. Thanks again, OM. 73 de WA2RSX VinceB. On 6/13/2021 9:01 AM, Hylton Conacher (ZR1HPC) wrote: Hi Vince, On 2021/06/12 17:20, Vince@Verizon wrote: Hello: Using LO vers 7.1.3.2 (x64) on desktop Windows 10 Home (64-bit). I would greatly appreciate receiving an explanation of a formula or function in Calc that updates the data within cell $Y$4 each time the data in column [M] changes. The formula in $Y$4 =$M18-$W$4. [M] [Y] Row 18 --> Row 4 Row 19 --> Row 4 Row 20 --> Row 4 Row 21 --> Row 4 Row 21 --> Row 4 Where is column W in your layout? The below is done on assumption. A simple sum will do it i.e.: In cell Y4 insert everything between the " "=SUM(M18-$W$4)" In cell Y5 "=SUM(M19-$W$4)" In theory you could just drag the formula from cell Y4 down to the bottom and then just change those M reference cell numbers. Remember that putting a $ before either a letter or number reference of a cell LOCKS that formula i.e. dragging the formula down a column to populate cells below will keep whatever is prefixed with $. Hope it helps Hylton -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
[libreoffice-users] Updating Data in Calc
Hello: Using LO vers 7.1.3.2 (x64) on desktop Windows 10 Home (64-bit). I would greatly receiving an explanation of a formula or function in Calc that updates the data within cell $Y$4 each time the data in column [M] changes. The formula in $Y$4 =$M18-$W$4. [M] [Y] Row 18 --> Row 4 Row 19 --> Row 4 Row 20 --> Row 4 Row 21 --> Row 4 Row 21 --> Row 4 TIA; VinceB. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
