Ask Your Question

How to make relative sheet addresses absolute leaving the cell range addresses as they are? (Was: How to copy-paste an improperly-formatted formula between sheets?)

asked 2016-06-18 01:56:30 +0200

goose121 gravatar image

updated 2016-07-30 11:46:55 +0200

Lupp gravatar image

When I made the formula, I forgot to make the sheet references absolute. Now, I have the same problem as this user. In addition, it is a really long and bear-like formula, and I would not like to go through it by hand and replace references, but rather than convert it into absolute references, which I told it to do, Find/Replace converts all of the references into #REF!. How can I do this, short of typing in all of the dollar signs myself?

EDIT: I had regexes on and I used Find: [^$]Menu and Replace:$Menu. I did also try escaping the dollar signs with a backslash

edit retag flag offensive close merge delete


What's your LibreOffice version?, can you show some of the formulas to modify?

m.a.riosv gravatar imagem.a.riosv ( 2016-06-18 14:15:58 +0200 )edit

I'm on Build e5f16313668ac592c1bfb310f4390624e3dbfb75. My formula is this: =INDIRECT(ADDRESS(3,MATCH(Menu.$C$2,$B$1:$H$1,0)+1))+INDIRECT(ADDRESS(3,MATCH(Menu.$D$2,$B$1:$H$1,0)+1))+INDIRECT(ADDRESS(3,MATCH(Menu.$E$2,$B$1:$H$1,0)+1))

goose121 gravatar imagegoose121 ( 2016-06-18 19:57:35 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2016-07-30 11:23:15 +0200

Lupp gravatar image

updated 2016-07-30 12:49:39 +0200

(Coming about this old thread by accident:)
Simple solution: Select the cell containing the formula and hit 'F4' four times. This should also work for a multiple selection.

Commenting on the original attempt:
The OQ may have missed to choose 'Formulae' for the option 'Search in:'. In addition he missed to reinsert the character accepted for the [^\$] part of the RegEx to search for. This image was created with V5.2.0.3, but older version also know the options.
If the text Menu or whatever sheetname used, can also occur as a part of a text constant, it will get prefixed the $ sign there too. To avoid this might be not so easy.

edit flag offensive delete link more

answered 2016-06-19 11:41:38 +0200

m.a.riosv gravatar image

'Menu' must be a Named reference, you can change it in Menu/Insert/Names/Manage [Ctrl+F3].

edit flag offensive delete link more


How? Can a named reference be qualified by adding a cell address?

Lupp gravatar imageLupp ( 2016-07-30 11:08:09 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-06-18 01:56:30 +0200

Seen: 460 times

Last updated: Jul 30 '16