SoftMaker.com

I just tried importing an XLS file (converted from an ODS I had been working on in IBM Lotus Symphony and LibreOffice), and I've run into two major limitations.

First, I have a bunch of SUMPRODUCT formulas, which had worked great, but here they all return 0. In the manual it says "Empty cells, text strings, and logical values evaluate as zero". All of my SUMPRODUCT formulas begin with two comparisons, for example:


The idea is to sum all the expenses (from another sheet) which are within a range of dates. But I suppose that PlanMaker takes the first two arguments as zero, so that this function fails to work as intended.

Does this interpretation seem accurate, and if so: 1. Is there an easy workaround; 2. Will logical values be supported in the future?

(I had previously used DSUM to accomplish a similar task, but I found that the SUMPRODUCT formulas were much simpler and more flexible, and they didn't require maintaining and referring to a sheet full of criteria.)

Second, I had simplified my spreadsheet by combining all expense categories and subcategories into one sheet, rather than having a sheet for each category. So my Expense sheet has a Category column and a Subcategory column. The first column uses Input Validation, with a static list of Categories. The second column uses Input Validation, with the list determined by a formula, which looks at which Category is selected, and based on that, selects the appropriate list of Subcategories. This formula looks something like this:


Unfortunately, in PlanMaker, the Subcategory dropdown doesn't work: clicking on the arrow does nothing. I suppose it doesn't understand the formula, but why not?

Can you please send us the file to forum(at)softmaker.de? Thank you!

_________________
Sven Leßmann
SoftMaker Software GmbH

I'm getting ready to send a file which demonstrates these limitations. In the process, I discovered one of the reasons for the error with the input validation: PlanMaker had failed to convert the references to another sheet (within the input validation formula) into its own format. So what had been $Categories.$B$1 should have been converted to Categories!$B$1, but wasn't.

Now that I've fixed that, it gives me this error when I try to put the reformatted formula into Input Validation:

"Some formula elements cannot be used here: array arguments and union, intersection and range operators are not allowed."

Again I'll ask: is there any way around this? Or put another way, is there any way to make one dropdown conditional upon the selection from another dropdown?

This way one could have dropdowns in two successive columns:

Column 1:
CategoryA
CategoryB
CategoryC

Column 2, in a row where CategoryA is selected:
SubcategoryA1
SubcategoryA2
SubcategoryA3

Column 3, in a row where CategoryB is selected:
SubcategoryB1
SubcategoryB2
SubcategoryB3

etc.

In both Excel and the various versions of OpenOffice, this is possible using the Choose and Match formulas, as in the example in the post above. Is there any way to achieve a similar effect in PlanMaker?

I asked the developers and will report here as soon as I have an answer.

_________________
Sven Leßmann
SoftMaker Software GmbH

We haven't yet received an example file for this problem. Can you please send us one? Thank you!

_________________
Sven Leßmann
SoftMaker Software GmbH

I sent an example file on Sep 10, but accidentally from a different email address (gmail). Perhaps it got caught in your spam filter? Anyway, I'll try sending it again; let me know if it still doesn't come through.