Ask Your Question
0

Automatic Adding $ to formula in spreadsheet blocking me to use libreoffice

asked 2015-08-19 09:57:38 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Hi, Libre 5.0.0 I have added a formula for a cell successfully.The formula looks like as follows. =Setup.$D$8

When I save, spreadsheet in Windows 93-2003 format and open, the same formula becomes (=$Setup.$D$8).Please note "$" added in front of "Setup". This causes my automation script unable to read. As a workaround Every time before running automation I need to remove this addition "$" from all formulas then Save (but not close). This is blocker for me to use. This is not seen in microsoft office.

Please implement change and suggest good workaround.Thanks.

[edit: added windows + lo5 tags]

edit retag flag offensive close merge delete

Comments

1

Don't save in Windows 93-2003

karolus gravatar imagekarolus ( 2015-08-19 11:00:12 +0200 )edit

If you want to get advice concerning a possible workaround you should explain for what reasons you

1) ...are using the alien persistent format ["Microsoft Excel 1997-2003 (.xls)" I assume.]

2) ...need the sheet part of the address being relative.

A workaround should be the only way. See my answer for the reasons.

Lupp gravatar imageLupp ( 2015-08-21 15:50:36 +0200 )edit

Hi Lupp,

Thanks for your response. Unfortunately I missed track. Actually I am doing automation testing using selenium and using jxl api to read and write excel. I definitely need to save spreadsheet in MS Excel 97-2003 format as this api doesn't support other versions of excel higher than this. Could you please provide work round.

Spreadsheet formula gravatar imageSpreadsheet formula ( 2015-12-17 13:35:55 +0200 )edit

Sorry! I missed your comment to date. I will append an amendment tro my answer now.

Lupp gravatar imageLupp ( 2015-12-30 18:13:46 +0200 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2015-08-21 15:58:57 +0200

Lupp gravatar image

updated 2015-12-30 18:30:38 +0200

(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. Both the 'F&R' dialogues are inserted into the attached example as images. ask56383SheetReferenceRelative001.xls

No guarantee of any kind! Errors expected!

edit flag offensive delete link more

Comments

Hi @Lupp

I did not test other than on your spreadsheet, so I do not guarantee treat all cases but it seems possible to use this:

Search For: ([^\.|\w])(\$) Replace With: $1

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-12-31 08:50:18 +0200 )edit

@pierre-yves samyn : Thank you for the suggestion. It seemed to be an opportunity to learn! However, I did not manage to understand. Do you mind to explain?
Regards

Lupp gravatar imageLupp ( 2015-12-31 12:18:39 +0200 )edit

@Lupp

It should not replace $ contained in the column/row references:

  • e.g. .$A in FirstSheet.$A$11, whe have a . before $
  • e.g. $11 in FirstSheet.$A$11, whe have a word character before $

The other may be replaced...

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-12-31 13:24:21 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-08-19 09:57:38 +0200

Seen: 212 times

Last updated: Dec 30 '15