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

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 close merge delete

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

( 2016-06-18 14:15:58 +0200 )edit

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

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

Sort by » oldest newest most voted

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.

more

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

more