Date: prev next · Thread: first prev next last
2021 Archives by date, by thread · List index

Den lör 25 sep. 2021 kl 22:07 skrev Steve Edmonds <>:

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.

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.


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:
Posting guidelines + more:
List archive:
Privacy Policy:

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.