Different Quotes/formula incompatibility with Excel.

[spira_post]

It seems that PM does not use quotes the same way as Excel within formulas that refer to a series of sheets. This causes sheets created in PM to be broken in Excel. It also breaks files created in Excel if they are opened and saved in PlanMaker subsequently.

Example: Say I want to have the sum of all cells A1 from a sheet named One up to a sheet named Three. This would be the formula:

=sum(One:Three!A1)

This would work fin in both Excel and PlanMaker.

But if the name of the starting or ending sheet has characters that required the sheet name to be placed in single quotes, things go awry. Here's some conditions that seem to require a sheet name to be placed within single quotes:
- Sheet name starts or ends with a number. ex. '123' or '123ndStreet'
- Sheet name has a space. ex. '123 Privet Drive'
- Sheet name has a single quote within it. ex. Aujourd'hui (today, in French)

PlanMaker seems to encapsulate each term of the selection within its own quotes (and throws an error in Excel):
=sum('1':'99'!A1)

While Excel encapsulates all of the selection within quotes:
=sum('1:99'!A1)

For the quotes within a sheet name, Planmaker will show this in the program (throws an error in Excel):
=sum(Hier:"aujourd'hui") // Hier:[double quotes]aujourd[singlequote]hui[double quotes]
But save it like this:
SUM(Hier:'Aujourd''hui'!A1) // all single quotes

Excel, once again, expects all of the selection to be put within single quotes, like this:
=sum('Hier:Aujourd''hui'!A1) // [singlequote]Hier:Aujourd[singlequote][singlequote]hui[singlequote]

I guess this can get a bit confusing, so I created a .xlsx file in PlanMaker that showcases all the problems mentioned. When I open the file in Excel 2011, I get many errors.

Here's a link to the file:
https://drive.google.com/file/d/12ZhdFw ... sp=sharing

A screen capture of the file opened in PlanMaker:
https://drive.google.com/file/d/11X6a16 ... sp=sharing

A screen capture of the file opened in Excel 2011:
https://drive.google.com/file/d/1-Ten-O ... sp=sharing

[SuperTech]

Thanks for reporting this problem. I was able to reproduce it and forwarded the details to our development team for further investigation.

[SuperTech]

Our development team has fixed this problem and the solution will be included in the next service pack of SoftMaker Office.

[spira_post]

Thank you!