Ask Your Question

When I write a formula in 1 tab, copy & paste it into another tab it links to it?

asked 2019-11-08 23:36:52 +0100

CdnTrader gravatar image

The new formula includes the Tab name in the pasted formula... I don't want that. Sample: the RY shouldn't be there ... The program added it automatically when it was pasted into a new Tab! How do I STOP this?


edit retag flag offensive close merge delete


What Version of LibO? What OS?
With what software(and what version of it was the document created? Was it at some time converted to or from or back and forth using an alien "file format" (.xls extension e.g.) in-between? (Dangerous! Bad influnce often!)
I don't know any recent version of Excel, but:
Older Excel didn't support relative sheet addressing. This forced OOo, LibreOffice, AOO to set an absolute sheet address (That's what the "$" in front of the sheetname does.) in every case when importing from Excel. Once exported to Excel "formats" a relative sheet address got absulte therefore, and the change was irreversible eben if later an attempt was made later to conver back.

Lupp gravatar imageLupp ( 2019-11-09 10:46:39 +0100 )edit

BTW: A spreadsheet has a tab, it isn't one. The tab is used as a kind of handle for selecting the sheet and as a sensitive area for calling the sheet's context menu with a rightclick.
I have a mouth. That doesn't mean I am one.

Lupp gravatar imageLupp ( 2019-11-09 11:24:10 +0100 )edit

I think there was an issue in some LibreOffice version, but solved time ago.

m.a.riosv gravatar imagem.a.riosv ( 2019-11-09 12:57:30 +0100 )edit

There is a faint remembrance of such a bug in my mind: By the respective version direct references in formulas were always shown with their complete address including the sheet part. However, I don't think that bug created absolute sheet addressing. (Not sure.)

Lupp gravatar imageLupp ( 2019-11-09 15:25:14 +0100 )edit

Yeah I vaguely remember such bug from version 5.x (or even 4.x?), and yes, IIRC it made absolute sheet references appear.

erAck gravatar imageerAck ( 2019-11-09 22:47:10 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-11-09 09:34:49 +0100

Lupp gravatar image

updated 2019-11-09 11:19:42 +0100

If you have just one such formula, simply remove the "$" by editing. If there are many:
Use Find & Replace with Search in: Formulasselected,
Regular expression not enabled.
Find = $RY Replace = RY
Replace All if you actually want the "All". Otherwise one by one.

If you also need to do it for more than one sheetname in one go you would need to use regular expressions (unfortunately rather complicated if you cannot asssure that sheet names are free of fullstops, apostrophes and the like. ¡¡Never use crazy sheetnames!!)

Regular expression enabled now.
Find = \$((([^\.]+)|('(''|[^'])+'))\.) (Not the only way top do it!)
Replace = $1

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-11-08 23:36:52 +0100

Seen: 22 times

Last updated: Nov 09