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?) [closed]

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

goose121 gravatar image

updated 2020-08-11 17:16:16 +0200

Alex Kemp 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 reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-11 17:16:27.556179


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 2020-08-11 17:14:45 +0200

Alex Kemp gravatar image

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

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

Question Tools

1 follower


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

Seen: 694 times

Last updated: Aug 11