Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

(I virtually never worked with MS Excel , but I was told...)

...that this marvelous software up to 2003 never supported relative sheet addressing. With other words: For Excel, and thus for a spreadsheet saved in an Excel format of that time, being absolute was implicit for the sheet part of references. LibreOffice Calc which would interpret Setup.$D$8 being relative with respect to sheets must therefore change it into $Setup.$D$8 when working with the alien filr format to get the same result as if the sheet was opebed with Excxel.

(I virtually never worked with MS Excel , but I was told...)

...that this marvelous software up to 2003 never supported relative sheet addressing. With other words: For Excel, and thus for a spreadsheet document saved in an Excel format of that time, being absolute was implicit for the sheet part of references. LibreOffice Calc which would interpret Setup.$D$8 being relative with respect to sheets must therefore change it it into $Setup.$D$8 when working with the alien filr format to get the same result as if the sheet was opebed with Excxel.Excxel.

This is one of the cases where free software wrongly gets charged with the consequences of shorttcomings of MS Office.

(I virtually never worked with MS Excel , but I was told...)

...that this marvelous software up to 2003 never supported relative sheet addressing. With other words: For Excel, and thus for a spreadsheet document saved in an Excel format of that time, being absolute was implicit for the sheet part of references. LibreOffice Calc which would interpret Setup.$D$8 as being relative with respect to sheets must therefore change it into $Setup.$D$8 when working with the alien filr format to get the same result as if the sheet was opebed with Excxel.

This is one of the cases where free software wrongly gets charged with the consequences of shorttcomings of MS Office.

(I virtually never worked with MS Excel , but I was told...)

...that this marvelous software up to 2003 never supported relative sheet addressing. With other words: For Excel, and thus for a spreadsheet document saved in an Excel format of that time, being absolute was implicit for the sheet part of references. LibreOffice Calc which would interpret Setup.$D$8 as being relative with respect to sheets must therefore change it into $Setup.$D$8 when working with the alien filr file format to get produce the same result behaviour as if the sheet was opebed opened with Excxel.

This is one of the cases where free software wrongly gets charged with the consequences of shorttcomings of MS Office.. (They often didn't even worry about the consequences of changes in their own specifications with respect to the use of an old file with a new version of their software.)

(I virtually never worked with MS Excel , but I was told...)

...that this marvelous software up to 2003 never supported relative sheet addressing. With other words: For Excel, and thus for a spreadsheet document saved in an Excel format of that time, being absolute was implicit for the sheet part of references. LibreOffice Calc which would interpret Setup.$D$8 as being relative with respect to sheets must therefore change it into $Setup.$D$8 when working with the alien file format to produce the same behaviour as if the sheet was opened with Excxel.

This is one of the cases where free software wrongly gets charged with the consequences of shorttcomings of MS Office. (They often didn't even worry about the consequences of changes in their own specifications with respect to the use of an old file with a new version of their software.)

(Editing:)
I reproduced your problem with LibO5.0.4 saving to xls (Excel 1997~2003) and reloading. My file was, of course, never edited with Excel itself.
The reloaded file showed the "absolutised" sheet references. I only can suggest a workaround using 'F&R' with a regular expression searching also into formulae in all sheets. This works fine for sheet names not needing a syntactical disambguation by single quotation marks (apostrophes) in one pass. I then completed it to also find the "dumb beginner's sheet names" containing bad characters. This worked, too, but needed more than one passes for formulae containing more than one references with sheet part. I could not find the reason. (Possibly a flaw of the regex engine?)

I you (someone else) were smart enough to avoid silly complications with sheet names, you may use the simpler variant. Bot 'F&R' dialogues are inserted into the attached example as images. ask56383SheetReferenceRelative001.xls

(I virtually never worked with MS Excel , but I was told...)

...that this marvelous software up to 2003 never supported relative sheet addressing. With other words: For Excel, and thus for a spreadsheet document saved in an Excel format of that time, being absolute was implicit for the sheet part of references. LibreOffice Calc which would interpret Setup.$D$8 as being relative with respect to sheets must therefore change it into $Setup.$D$8 when working with the alien file format to produce the same behaviour as if the sheet was opened with Excxel.

This is one of the cases where free software wrongly gets charged with the consequences of shorttcomings of MS Office. (They often didn't even worry about the consequences of changes in their own specifications with respect to the use of an old file with a new version of their software.)

(Editing:)
I reproduced your problem with LibO5.0.4 saving to xls (Excel 1997~2003) and reloading. My file was, of course, never edited with Excel itself.
The reloaded file showed the "absolutised" sheet references. I only can suggest a workaround using 'F&R' with a regular expression searching also into formulae in all sheets. This works fine for sheet names not needing a syntactical disambguation by single quotation marks (apostrophes) in one pass. I then completed it to also find the "dumb beginner's sheet names" containing bad characters. This worked, too, but needed more than one passes for formulae containing more than one references with sheet part. I could not find the reason. (Possibly a flaw of the regex engine?)

I you (someone else) were smart enough to avoid silly complications with sheet names, you may use the simpler variant. Bot Both the 'F&R' dialogues are inserted into the attached example as images. ask56383SheetReferenceRelative001.xls

No guarantee of any kind! Errors expected!