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?)

[goose121]

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

[mariosv]

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

[goose121]

I’m on 5.0.3.2 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))

[mariosv]

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

[Lupp]

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

[Lupp]

(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: activated screenshot)