CELL function - Worksheet name not recognized [SOLVED]

Post your questions, bug reports and suggestions here for PlanMaker HD for Android.

Moderator: SoftMaker Team

Post Reply
bsbd
Posts: 39
Joined: Fri Apr 03, 2015 5:51 pm

CELL function - Worksheet name not recognized [SOLVED]

Post by bsbd » Wed Sep 02, 2015 9:08 pm

I've been troubleshooting the cause of broken links in an important Excel files when opened in PlanMakerHD. I've finally I tracked it down to the fact that the optional Reference field in Excel's CELL function isn't being fully resolved for the "filename" Type. PlanMaker will ignore any alternate worksheet name prefix.

For a simple example, I created a file named planmakerHD_test.xlsx containing this formula anywhere on Sheet2 referencing a cell on Sheet1:

=CELL("filename",Sheet1!$A$1)

Excel on a PC returns "<path>\[planmakerHD_test.xlsx]Sheet1".

PlanMaker on an Android tablet returns "<path>/[planmakerHD_text.xlsx]Sheet2"


I've confirmed that this problem also occurs with the Planmaker Mobile Free android app.


Why I would love to see this fixed:

I know the cell on Sheet2 could be hard-coded with the name of the other worksheet, but the method I use is popular for maintaining integrity of Sheet2's other formulas if the target worksheet name is ever edited to something different. In fact, this is the full formula my main Excel file uses to parse out everything but the other worksheet's name:

=MID(CELL("filename",Sheet1!$A$1), FIND("]", CELL("filename",Sheet1!$A$1)) + 1, 31)
Last edited by bsbd on Tue Oct 20, 2015 3:52 pm, edited 1 time in total.

sven-l
SoftMaker Team
SoftMaker Team
Posts: 7886
Joined: Wed Apr 09, 2008 8:26 am
Location: Nuremberg
Contact:

Re: CELL function - Worksheet name not recognized

Post by sven-l » Mon Sep 07, 2015 9:04 am

Could you please provide a sample file (attach it here or send one to forum(at)softmaker.de)? Thank you!
Sven Leßmann
SoftMaker Software GmbH

bsbd
Posts: 39
Joined: Fri Apr 03, 2015 5:51 pm

Re: CELL function - Worksheet name not recognized

Post by bsbd » Mon Sep 07, 2015 9:12 pm

Thanks Sven. I just emailed a sample spreadsheet that I edited-down from my master Excel investments spreadsheet. You should see an embedded "Notes to SoftMaker" box when you open the file up that clearly describes the issue. (There's also a suggestion about stock quotes, but I've already seen it said elsewhere on the forum that it's on the drawing board).

I've discovered that worksheet prefixes are recognized for the CELL function "contents" type, I just need it to work for the "filename" type. Those are the only two variations of the CELL formula I've tried though.

Thanks,

-bsbd


P.S. You may get an exection warning when you open in Excel due to the stock quote fetching formulas on the Summary tab.

sven-l
SoftMaker Team
SoftMaker Team
Posts: 7886
Joined: Wed Apr 09, 2008 8:26 am
Location: Nuremberg
Contact:

Re: CELL function - Worksheet name not recognized

Post by sven-l » Tue Sep 08, 2015 10:13 am

Thank you for the sample file! I reported that issue to our developers and will inform you here as soon as we have a solution.
Sven Leßmann
SoftMaker Software GmbH

sven-l
SoftMaker Team
SoftMaker Team
Posts: 7886
Joined: Wed Apr 09, 2008 8:26 am
Location: Nuremberg
Contact:

Re: CELL function - Worksheet name not recognized

Post by sven-l » Mon Sep 14, 2015 3:08 pm

The problem has been solved and should no longer occur with the next service pack.
Sven Leßmann
SoftMaker Software GmbH

bsbd
Posts: 39
Joined: Fri Apr 03, 2015 5:51 pm

Re: CELL function - Worksheet name not recognized

Post by bsbd » Mon Sep 14, 2015 3:12 pm

Thanks very much Sven, I'll anxiously await the update.

bsbd
Posts: 39
Joined: Fri Apr 03, 2015 5:51 pm

Re: CELL function - Worksheet name not recognized [SOLVED]

Post by bsbd » Tue Oct 20, 2015 3:58 pm

This issue appears to be fixed in the October 17 update for PlanMaker HD, version 745.1010.gs. I haven't received an update for PlanMaker Mobile Free yet, so the problem still exists in that app.

Post Reply

Return to “PlanMaker HD”