[libreoffice-users] extracting cell values
Users Ahoy: Is there a way to get LO (v5.2.7.2) calc to extract the numerical value from a cell, and use that instead of the cell reference in further calculations? Frinstance, if I have 167 in cell A1, and want 16.7 in A2, how can I program it, (if at all)? If I put =A1/10 into A2, A2 will contain A1/10, not 16.7,even though it will display as 16.7. I want to get away from cell references and use the contained values. Can I? I thought that =VALUE(A1)/10 might work, but the result continues to use the cell reference, not the contained value. Miss K -- 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] extracting cell values
Original Message From: mxk [mailto:mxkeat...@comcast.net] Sent: Saturday, September 25, 2021, 16:02 UTC To: users@global.libreoffice.org Subject: [libreoffice-users] extracting cell values > Users Ahoy: > > Is there a way to get LO (v5.2.7.2) calc to extract the numerical value > from a cell, and use that instead of the cell reference in further > calculations? > > Frinstance, if I have 167 in cell A1, and want 16.7 in A2, how can I > program it, (if at all)? > > If I put =A1/10 into A2, A2 will contain A1/10, not 16.7,even though it > will display as 16.7. > > I want to get away from cell references and use the contained values. > Can I? > > I thought that =VALUE(A1)/10 might work, but the result continues to use > the cell reference, not the contained value. > > Miss K If you don't want to use cell references, how would you inform Calc which one of the thousands of cells in a sheet was the one holding the value you wanted it to use in your calculations? If you actually do see A1/10 in cell A2, this suggests that cell A1 is formatted as text not a numeric value. In which case =VALUE(A1)/10 does work. You will find some excellent Calc User Guides here: https://tinyurl.com/aa4mrlt LibreOffice version 5.2.7.2 is quite old and I would recommend updating to at least version 7.1.6 from: https://www.libreoffice.org/download/download/ Dave -- 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] extracting cell values
Do you have the following checked: TOOLS -> OPTIONS -> LIBREOFFICE CALC -> VIEW Value highlighting? If so, uncheck it. Joe On 9/25/21 11:08 AM, Dave Barton wrote: Original Message From: mxk [mailto:mxkeat...@comcast.net] Sent: Saturday, September 25, 2021, 16:02 UTC To: users@global.libreoffice.org Subject: [libreoffice-users] extracting cell values Users Ahoy: Is there a way to get LO (v5.2.7.2) calc to extract the numerical value from a cell, and use that instead of the cell reference in further calculations? Frinstance, if I have 167 in cell A1, and want 16.7 in A2, how can I program it, (if at all)? If I put =A1/10 into A2, A2 will contain A1/10, not 16.7,even though it will display as 16.7. I want to get away from cell references and use the contained values. Can I? I thought that =VALUE(A1)/10 might work, but the result continues to use the cell reference, not the contained value. Miss K If you don't want to use cell references, how would you inform Calc which one of the thousands of cells in a sheet was the one holding the value you wanted it to use in your calculations? If you actually do see A1/10 in cell A2, this suggests that cell A1 is formatted as text not a numeric value. In which case =VALUE(A1)/10 does work. You will find some excellent Calc User Guides here: https://tinyurl.com/aa4mrlt LibreOffice version 5.2.7.2 is quite old and I would recommend updating to at least version 7.1.6 from: https://www.libreoffice.org/download/download/ Dave -- 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] extracting cell values
Hi Miss K. The purpose of your request is not clear. For the situation where I want to de-reference a value I use copy and then paste-special selecting only numbers. If you want 167 in A1 and 16.7 in A2 so that is stays 16.7 even if A1 changes then possibly in B1 put =A1/10 then copy B1 and in A2 paste-special selecting only numbers to paste. I arrange the type of calculation above so that I can copy/paste-special rows or columns at a time. Steve On 26/09/2021 05:02, mxk wrote: Users Ahoy: Is there a way to get LO (v5.2.7.2) calc to extract the numerical value from a cell, and use that instead of the cell reference in further calculations? Frinstance, if I have 167 in cell A1, and want 16.7 in A2, how can I program it, (if at all)? If I put =A1/10 into A2, A2 will contain A1/10, not 16.7,even though it will display as 16.7. I want to get away from cell references and use the contained values. Can I? I thought that =VALUE(A1)/10 might work, but the result continues to use the cell reference, not the contained value. Miss K -- 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] extracting cell values
At 12:02 25/09/2021 -0400, Miss Keating wrote: Is there a way to get LO (v5.2.7.2) calc to extract the numerical value from a cell, and use that instead of the cell reference in further calculations? Frinstance, if I have 167 in cell A1, and want 16.7 in A2, how can I program it, (if at all)? If I put =A1/10 into A2, A2 will contain A1/10, not 16.7,even though it will display as 16.7. I want to get away from cell references and use the contained values. Can I? I thought that =VALUE(A1)/10 might work, but the result continues to use the cell reference, not the contained value. I *think* this is a contradiction, as any formula you put in A2 - whatever it is - will necessarily continue to refer to A1 and its value be changed when the value in A1 is modified. That's what spreadsheets do! But there is a workaround: o Enter your formula as usual. o Fill it down columns or across rows as appropriate. o Select the range of cells that you wish to freeze. o Copy that range. o Paste the values back into the same range, but using Paste Special instead of ordinary Paste, and ensuring that Formulae is *not* ticked in the Paste Special dialogue. Ana alternative would be to make a copy of your original range of data values and freeze those. Then you could base your subsequent calculations on the cells containing those frozen values instead of on the originals. The copied values could be visible on the same sheet, or hidden away elsewhere on the same sheet or another sheet. You could use Print Ranges to suppress printing of whichever values you did not want to see. I trust this helps. Brian Barker -- 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] extracting cell values
Den lör 25 sep. 2021 kl 22:07 skrev Steve Edmonds < steve.edmo...@ptglobal.com>: > Hi Miss K. > The purpose of your request is not clear. > For the situation where I want to de-reference a value I use copy and > then paste-special selecting only numbers. > If you want 167 in A1 and 16.7 in A2 so that is stays 16.7 even if A1 > changes then possibly in B1 put =A1/10 then copy B1 and in A2 > paste-special selecting only numbers to paste. > I arrange the type of calculation above so that I can copy/paste-special > rows or columns at a time. > Steve > > For some reason I never got the original message, which is why I respond to this one instead. > On 26/09/2021 05:02, mxk wrote: > > Users Ahoy: > > > > Is there a way to get LO (v5.2.7.2) calc to extract the numerical > > value from a cell, and use that instead of the cell reference in > > further calculations? > Well, that kind if defeats the purpose with a spreadsheet in the first place, so you just have to do that manually, or write a macro that does that for you. > > > > Frinstance, if I have 167 in cell A1, and want 16.7 in A2, how can I > > program it, (if at all)? > There are probably hundreds of ways to do that, but one that comes to mind is creating a macro that dereferences everything in the currently selected cell. That should work relatively smoothly, no need to copy and paste inside formulas. So in A2 first do it the traditional way, enter =A1/10. then keep A2 selected and then run your macro (which you can associate to a button, menu option or a keyboard shortcut or even an event. The macro should then search the formula for references, find the value in the cells they are referencing and replace the references with those values, in this case change the formula to =167/10. The problem is that you have to write that macro, which isn't that hard, but it doesn't write itself, of course… > > > > If I put =A1/10 into A2, A2 will contain A1/10, not 16.7,even though > > it will display as 16.7. > Correct. > > > > I want to get away from cell references and use the contained values. > > Can I? > Not easily using existing functionality directly, but you can make it happen, see above. > > > > I thought that =VALUE(A1)/10 might work, but the result continues to > > use the cell reference, not the contained value. > Correct, and it's by design. > > > Miss K > Kind regards Johnny Rosenberg > > > > > > > -- > 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 > -- 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] extracting cell values
Hello, You can convert the formulas to only keep the values by doing a copy/paste special where what you will keep are only the numbers. To do: select the cells, copy (CTRL-C or Edit > Copy) and then paste special (Edit > Paste Special), and select to keep only the numbers. You may be prompted to inform you that you are pasting over existing data: if so, just accept. Then, if you look in the cells, you should now only have the results of the cells. I recommend you paste to a new worksheet to keep the formulas intact for a future use. I hope this helps. Rémy. Le dimanche 26 septembre 2021 à 09:05 +1300, Steve Edmonds a écrit : > Hi Miss K. > The purpose of your request is not clear. > For the situation where I want to de-reference a value I use copy and > then paste-special selecting only numbers. > If you want 167 in A1 and 16.7 in A2 so that is stays 16.7 even if A1 > changes then possibly in B1 put =A1/10 then copy B1 and in A2 > paste-special selecting only numbers to paste. > I arrange the type of calculation above so that I can copy/paste- > special > rows or columns at a time. > Steve > > On 26/09/2021 05:02, mxk wrote: > > Users Ahoy: > > > > Is there a way to get LO (v5.2.7.2) calc to extract the numerical > > value from a cell, and use that instead of the cell reference in > > further calculations? > > > > Frinstance, if I have 167 in cell A1, and want 16.7 in A2, how can > > I > > program it, (if at all)? > > > > If I put =A1/10 into A2, A2 will contain A1/10, not 16.7,even > > though > > it will display as 16.7. > > > > I want to get away from cell references and use the contained > > values. > > Can I? > > > > I thought that =VALUE(A1)/10 might work, but the result continues > > to > > use the cell reference, not the contained value. > > > > Miss K > > > > > > -- 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] extracting cell values
On Sun, 26 Sep 2021 02:33:33 +0200 Johnny Rosenberg wrote: Hello, >Well, that kind if defeats the purpose with a spreadsheet in the first >place, so you just have to do that manually, or write a macro that does >that for you. OP's request whiffs of an XY problem to me. Of course, I may be wrong. -- Regards _ / ) "The blindingly obvious is never immediately apparent" / _)rad "Is it only me that has a working delete key?" But they didn't tell him the first two didn't count Tin Soldiers - Stiff Little Fingers -- 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] extracting cell values
Hello Miss K. The result, 16.7, may be pasted into another cell using "Paste value". This will not paste the formula. If your result cell is displaying the formula and not the result then there is a Calc setting to change. Even if you are seeing the formula, using "Paste value" will paste the value and not the formula. Also, if pasting into another program, e.g. an email, Paste will paste the value not the formula. If you see a formula when you look at your result cell then the Calc options have been set to display formulas, not results. To return Calc to the usual setting, display result instead of formula, do the following: From the menu select Tools > Options In the dialog displayed Open the Calc branch, select View, and make certain the "Formulas" option is not checked. Click OK to finish. On Sat, Sep 25, 2021 at 12:04 PM mxk wrote: > Users Ahoy: > > Is there a way to get LO (v5.2.7.2) calc to extract the numerical value > from a cell, and use that instead of the cell reference in further > calculations? > > Frinstance, if I have 167 in cell A1, and want 16.7 in A2, how can I > program it, (if at all)? > > If I put =A1/10 into A2, A2 will contain A1/10, not 16.7,even though it > will display as 16.7. > > I want to get away from cell references and use the contained values. Can > I? > > I thought that =VALUE(A1)/10 might work, but the result continues to use > the cell reference, not the contained value. > > Miss K > > > -- > 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 > -- Alan Boba CISSP, CCENT, ITIL v3 Foundations 2011 -- 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