How to copy the data from cell to an other not the formula?

In its simplest possible form: lets say C1 has this formula in it A1*B1. I would the like to copy the result of that calculation to an other cell. I could put =(C1) in this cell but this would copy the formula and I want only the data.

The difference is crucial because the value in A1 and B1 updates continuously from an online source.

Ideally the result continuously generated in C1 should be saved in a column ether in the same sheet or an other one.

If there’s a way to fix this I would be very happy!

The simplest and, judging by your description of the data, the only correct way is to copy cell C1, select “an other cell” and execute Edit - Paste Special - Paste Special (or Ctrl + Shift + V) - Values Only.
Since you say

only you know at what point in time and in which cell you need to insert the result A1*B1

Or, if destination cell is not formatted as Text, Ctrl+Alt+Shift+V.

This would be how it would look at two different points in time:


C3 is the product of A2 and A3. A2 change because this cell is derive from an online score and this will change the value in C3 as well every time A2 is updated.

Perhaps a formula can move the value in C3 to an other cell (in this case D3,D4… ) every time a new value i generated in C3? If this works once perhaps that formula can simply be copied trough out the entire column? And the new permanent table I want would be created?

Sure there are better (automatic) ways to do that, but…

You can fill D3:D28 with the formula =$C$3. Then when a new value is added, you can convert the new result (D3 and below) to value.

To convert a formula to value, there are a few option. Here some:

  • Edit the formula in D3 (F2), calculate de value (F9), accept (Enter, Enter)
  • Copy the formula in D4 (Ctrl+V), and paste as unformatted text (Ctrl+Alt+Shift+V)
  • Copy the formula in D5 (Ctrl+V), and paste special with default options (Ctrl+Shift+V), accept (Enter)
  • Active the cell D6, choose menu Data - Calculate - Formula to Value (the formula will be seen in the input line while this cell remains active)

Also you can skip C3, and fill D3:D28 with the formula =$A$2*$A$3.

Crafty but still manual

Please leave this venture - the idea from the very beginning was not correct.
Let’s look at a specific example. You say

Perfectly. Let the macro remember each new calculated product in the next cell in column D. Once again - “each new”. Now imagine that you get two or more SAME NUMBERS “from an online score”. Well, for example, the number 10 five times in a row. Would you expect five cells to be filled with the value 4440? I assure you, Calc does not expect this. And if you try to teach him “yes, the same numbers should be entered”, then be prepared for the fact that Calc will fill the entire D column with the same value in a matter of seconds.

To perform tasks similar to the one you described, LibreOffice has a special Base component. Yes, it will take time to master. It will even be a little more difficult than writing macros. But - just believe! - Calc is made for other tasks: the input form to fill in the data table isn’t Calc’s task.

Im optimistic and what eeigor put together in his formula isent much less complex. And I would have grate use for sutch a formula in calc . Im sure you have a point tough and offcource there are more complex solutions that also require more skilles.

I have noticed that the data input can be delayed and not delivers in chunks witch I presume is huge advantage here.

Apparently my English is really bad if I can’t explain a simple think.

I’ll try again. Please describe the user action (you or another person) when you receive two invoices with the same value. What should a person do? Clear the old value and enter it again? With this action, the macro will add a cell with a value of 0 and another cell with the same value as before. Or will you press some button to force the macro to enter the current value? If so, then you need both a function and a button that will run a macro to copy a value from one cell to another, right? But if you have a button, why do you need a function?!

1 Like

First copy your cell C1. Then, if you go to Edit>Paste Special, you should be given “Paste Only Numbers” among the options offered.

@Jalle, you need a macro that will automatically track the change in cell C3 and copy the new value to column D.
Use “com.sun.star.awt.XCallback”.
The conceptual solution is given here. But the code needs to be simplified. You only have one traceable cell.

Edit:
File attached.
save-value (2).ods (15.6 KB)
You can rearrange the cells. The target range must be bounded on the left and right by empty columns and there must be nothing below it.

cool that its possible.

so the formula would be like this: =SAVEDATA(C3;“D5:D28”;D2)

But the macro? Its not easy.

I’ll answer later, if others don’t help.

Try example file above (see solution).

Nothing happens in the target cell. I try to open macro but found nothing

I’ll watch it tonight.

Thanks. Im looking forward to it!

save-value (2).ods (14.2 KB)
Updated

Function SaveValue(Watch, Target$)
'''	Args:
'''		Watch: Source cell to copy&save its value to new cell of target range.
'''		Target: Left-top cell of target range.

	Dim ac As Object, callback As Object
	
	ac = CreateUnoService("com.sun.star.awt.AsyncCallback")
	callback = CreateUnoListener( "callback_", "com.sun.star.awt.XCallback")
	ac.addCallback(callback, Array(Watch, Target))
End Function

Sub callback_notify(aData)
	Dim sheet As Object, cursor As Object
	Dim lefttopcell As Object, newcell As Object
	Dim nCol&, nEndRow&
	Dim value: value = aData(0)

	On Local Error Resume Next
	sheet = ThisComponent.CurrentController.ActiveSheet  '.Sheets.getByName("Sheet1")
	lefttopcell = sheet.getCellRangeByName(aData(1))
	nCol = lefttopcell.CellAddress.Column

	cursor = sheet.createCursorByRange(lefttopcell)
	cursor.collapseToCurrentRegion()
	nEndRow = cursor.RangeAddress.EndRow

	newcell = sheet.getCellByPosition(nCol, nEndRow + 1)
	newcell.Value = value
End Sub


Edit: Cells A2 and A3 to change.

Method2 (Inspired by @Lupp). This method is preferable. Sheet2 contains a formula that calls a macro whenever the target range is updated.

Try this.
save-value (3).ods (14.3 KB)
No AsyncCallback. No listener used. A simple macro.

Function DoSaveValue(Watch, Target$)
	Dim result As Boolean
	result = SetValue(Array(Watch, Target))
	DoSaveValue = IIf(result, ":done:", ":fail:")
End Function

Function SetValue(aData) As Boolean
	On Local Error GoTo Fail
	Dim sheet As Object, cursor As Object
	Dim lefttopcell As Object, newcell As Object
	Dim nCol&, nEndRow&
	Dim value: value = aData(0)

	sheet = ThisComponent.CurrentController.ActiveSheet  '.Sheets.getByName("Sheet2")
	lefttopcell = sheet.getCellRangeByName(aData(1))
	nCol = lefttopcell.CellAddress.Column

	cursor = sheet.createCursorByRange(lefttopcell)
	cursor.collapseToCurrentRegion()
	nEndRow = cursor.RangeAddress.EndRow

	newcell = sheet.getCellByPosition(nCol, nEndRow + 1)
	newcell.Value = value
	SetValue = True

Fail:
End Function


Excel has an Application.Volatile method. In Calc, the macro is volatile by default.
NOTE: The algorithm uses the collapseToCurrentRegion() cursor method, so the target range must be surrounded by empty columns on the left and right.


And why did I do it differently before?

This is where it all comes from.

Edit:

Nuance. When you open the file, the ThisComponent.CurrentController.ActiveSheet method is not yet available, so an error occurs. But this is the desired behavior, because in this case the last value is not duplicated. And in cell A1 you see the result :fail:
When using the first method, this harmful effect did not occur, but only connected the listener.

Which method is better?