Re: [libreoffice-users] extracting cell values

Den lör 25 sep. 2021 kl 22:07 skrev Steve Edmonds <
steve.edmonds@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+unsubscribe@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+unsubscribe@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