extracting cell values

users
#1

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

#2

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

#3

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

#4

Do you have the following checked:

TOOLS -> OPTIONS -> LIBREOFFICE CALC -> VIEW

Value highlighting? If so, uncheck it.

Joe

#5

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

#6

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.

#7

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.

> 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

#8

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.....

#9

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.