External cell references not working

This beta forum will be closed soon. Please post in the regular forum for PlanMaker 2010 for Windows.

Moderator: SoftMaker Team

Post Reply
FabianFinlay
Posts: 10
Joined: Wed Oct 21, 2009 12:12 pm

External cell references not working

Post by FabianFinlay » Thu Nov 19, 2009 3:50 pm

I have a file with a lot of external cell references. Most of them work but two of them do not. These are the faulty ones

='C:\My Documents\Excel files\[BASERATE.xls]A'!BASERATE
or
='C:\My Documents\Excel files\[BASERATE.xls]'!BASERATE

Sheet A is the only sheet in baserate.xls so I have tried it with and without sheet references.
and

='C:\My Documents\Excel files\[SHARES.XLS]'!EURO

Most of the other cell references are to the same spreadsheet shares.xls and work fine eg

='C:\My Documents\Excel files\[SHARES.XLS]'!DCC which works fine.

='C:\My Documents\Excel files\[SHARES.XLS]'!EURO returns a value of zero whereas in the original file opened with Excel it returns 1.12

='C:\My Documents\Excel files\[BASERATE.xls]'!BASERATE returns 0.00% whereas it should return 0.50% and

='C:\My Documents\Excel files\[BASERATE.xls]A'!BASERATE returns #ref!

Both shares.xls and baserate.xls have only one worksheet.

It seems to make no difference whether these files are open or closed.

baserate.xls has no external links. I have tried substituting a standard cell reference for the named range

='C:\My Documents\Excel files\[BASERATE.xls]'!E236 This returns #ref!

Inserting an A in this formula causes the path reference to disappear so that it becomes

='[BASERATE.xls]A'!E236 which returns 0.00%

Changing the worksheet name to BASERATE

='[BASERATE.xls]BASERATE'!E236 returns #ref!

and ='[BASERATE.xls]'!E236 returns #ref!

changing the cell reference back to a named range causes the path reference to reappear

='C:\My Documents\Excel files\[BASERATE.xls]'!BASERATE and returns 0.00%

and

='C:\My Documents\Excel files\[BASERATE.xls]BASERATE'!BASERATE returns #ref!

all most mysterious

These formulas were imported when converting an existing Excel file to pmd but typing them from scratch makes no difference. In particular even if you type them without the full path because it is in the same folder as the file with the formula, Planmaker inserts the full path when it wants to.

Maybe there is a limit to how many external cell references you can have and I am just hitting the limit.

Indeed I have just realised that there are a whole load more that are not working. I think there are 23 unique references that are working though many of them are used more than once. There are at least as many that are not working.

martin-k
SoftMaker Team
SoftMaker Team
Posts: 2814
Joined: Fri Nov 21, 2003 4:57 pm
Location: Nürnberg, Germany
Contact:

Re: External cell references not working

Post by martin-k » Thu Nov 19, 2009 3:52 pm

Please send sample files to forum(at)softmaker.de referencing this thread.
Martin Kotulla
SoftMaker Software GmbH

FabianFinlay
Posts: 10
Joined: Wed Oct 21, 2009 12:12 pm

Re: External cell references not working

Post by FabianFinlay » Fri Nov 20, 2009 12:00 pm

This topic has been developed further in another thread I started on reopening saved xls files with Excel. That problem seems to relate to the use of external cell references and I have posted further details and illustrative spreadsheets there at

http://www.softmaker.com/forum/viewtopi ... 133&t=7286

Post Reply

Return to “Beta: PlanMaker 2010 for Windows”