Hi,
After importing an unformated file, I want to get the product of two columns with a basic formula (=D8*1000/E8) and Calc provides a correct result (say 31).
But when trying to copy/paste the same formula in the following rows, Calc shows the result of the first row (31) though the formula has been adapted to the new row (=D9*1000/E9) and the result should be diverse: If I write (as opposed to copy/paste) the formula in the following row instead, the correct result (say 9) is showing.
The spreadsheet contains 6 sheets, Calc was doing fine in the first five sheets (with the same formula) but not in the last one.
Any ideas?
OS=Windows 10
Libre Office Version : 6.3.0.4 (x64)
The asterisk is used for markup to create italics so you need to show your formulae as preformatted text; use the 101010 icon.
Please don’t use an answer for something that clearly isn’t an answer - add a comment to your question or edit the question as appropriate.
Any ideas?
yes - skip LibreOffice version 6.3.0.4 and update. From my perspective a buggy release not worth to further investigate any problems, which may be fixed in more recent releases.
Paste Special selecting Only Formulas no longer works for columns even with $A1 (relative). It works for rows in either A1, or $A1(relative). Libre Office version 24.2.3.2
Please test in safe mode and if reproducible then please file a bug report, How to Report Bugs in LibreOffice - The Document Foundation Wiki
Then post the bug report number here in the form of tdf#123456
Hello,
Sorry for the late answer, I found a workaround but will keep in mind your recommandation if a new issue.
Best, Ta
Hello,
Thanks and I had done your recommandation obviously, but it kept bugging. Actually not the first time it happens, especially with “heavy” files, i.e. over 100 rows and or columns.
Best, Ta
dbondo
June 14
ve3oat:
Paste Special and select Only Formulas.
Paste Special selecting Only Formulas no longer works for columns even with $A1 (relative). It works for rows in either A1, or $A1(relative). Libre Office version 24.2.3.2
Visit Topic or reply to this email to respond.
To unsubscribe from these emails, click here.
Without a sample, I would guess that the column has been formatted as a partial date like YY or M or similar, displaying incorrect results, see attached spreadsheet.
DateFormattingDisplaysIncorrectResult.ods (10.8 KB)
1 Like
Thank you for responding. I couldn’t find a way to send a *.ods copy in Bugzilla. Attached is an actual copy. It has always worked correctly up until this latest version.
Copy and paste Example.ods (35.8 KB)
Hi @dbondo . It is expected that Column G will always continue to give the same result as it is looking only at itself, =AVERAGE(G3:G18).
I don’t know if G is supposed to average B:F, or B:F over 15 days, or comes from elsewhere.
What is column G intended to do?
I think I have a similar problem with Copy Paste.
Copy & Paste Formatted Date is changing format from Format>Number>Date to the numeric format for date in both the copied cell and the pasted cell.
Here is the example of what it should be:
| 09/09/24 | 09/10/24 | 09/11/24 | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Mon | Tue | Wed |
Here is the example of what actually appears:
| 09/09/24 | 45,545.00 | 45,546.00 | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Mon | Tue | Wed |
What is really weird is that if copy and paste is done manually the paste works correctly. However, the copy and paste is a subroutnne in a macro that I’ve used for years and this only occurs when I run the complete macro. I can run the subroutine seperately using Paste or Paste Special and the strange formatting does not occur. However when the entire macro is run the strange reformatting of the copied source and the pasted results are always reformatted. I have no idea of any way to solve this, however I have forund a cumbefrson work around by placing the cursor on the row number that the numeric format and selecting Format>Number>Date, but since recording macros does not support mouse selections I can’t put this into a macro subroutine.
FYI: Here is the subroutine macro code:
sub CopyDateDay
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
rem ----------------------------------------------------------------------
dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = “By”
args1(0).Value = 3
args1(1).Name = “Sel”
args1(1).Value = false
dispatcher.executeDispatch(document, “.uno:GoLeft”, “”, 0, args1())
rem ----------------------------------------------------------------------
dim args2(1) as new com.sun.star.beans.PropertyValue
args2(0).Name = “By”
args2(0).Value = 11
args2(1).Name = “Sel”
args2(1).Value = false
dispatcher.executeDispatch(document, “.uno:GoUp”, “”, 0, args2())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = “By”
args3(0).Value = 1
dispatcher.executeDispatch(document, “.uno:GoDownSel”, “”, 0, args3())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, “.uno:Copy”, “”, 0, Array())
rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = “By”
args5(0).Value = 5
args5(1).Name = “Sel”
args5(1).Value = false
dispatcher.executeDispatch(document, “.uno:GoRight”, “”, 0, args5())
rem ----------------------------------------------------------------------
'dispatcher.executeDispatch(document, “.uno:Paste”, “”, 0, Array())
rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name = “Flags”
args6(0).Value = “SDFT”
args6(1).Name = “FormulaCommand”
args6(1).Value = 0
args6(2).Name = “SkipEmptyCells”
args6(2).Value = false
args6(3).Name = “Transpose”
args6(3).Value = false
args6(4).Name = “AsLink”
args6(4).Value = false
args6(5).Name = “MoveMode”
args6(5).Value = 5
dispatcher.executeDispatch(document, “.uno:InsertContents”, “”, 0, args6())
end sub
If desired to work on a solution, I can provide the spreadsheet and complete macro. If this is a bug then can someone get it into a bug report. Doing it in Bugzilla is beyond my limited ability.
Thanks in advance.
@dbondo You don’t have a similar problem; it is specifically about a macro.
Please create a new question and copy your question there. Afterwards you can delete your question here
Don’t use a simple paste operation. Use Paste Special and select Only Formulas.
If that really helped it would be bug though, copy&paste of entire cells including a formula with adapting relative references and giving correct result after should work and not need any Paste Special.
Hi and thank you for the answers.
For ve3oat, good to remind it, and actually I do so, using Ctrl+Shift+V in order to select an unformatted output / and or a specific item to paste.
To Opaque, I did upgrade to the latest version but I am not sur the older version was the reason. No definitive opinion, but I rather lean towards some corrupt/strangely formated data I have been pasting formerly.
My turnaound before upgrading to the newer LO version has been to open a new spreadsheet, paste data and do my calculations in it, and then copy paste the data in the older spreadsheet, and it worked.
Please do not post answers on this site, if you actually don’t answer a question (you own in this case). Use add a comment instead in such cases. Thanks in advance.
Sometimes, Calc might not automatically recalculate the cells after a copy-paste operation. You can force a recalculation by pressing Ctrl+Shift+F9.
Hello,
Thanks for that, actually I gave up, shut down LO, and copied data in a new tab. A workaround rather than an effective response.
Best, Ta