Excel and Dataviz Sheets To Go

[Greg Lovern]

I had an interesting experience today with Excel and Dataviz Sheets To
Go, a spreadsheet application for handheld mobile devices.

A client asked me to make an Excel spreadsheet with many formulas, for
use in their Blackberries. At the time, they were sure it was Excel on
their blackberries, but I was pretty sure it couldn't be, and
eventually it turned out to be Dataviz Sheets To Go. I never saw it
myself; the blackberries were used by people in the field while I was
only in contact with people in the office.

Dataviz claims Excel compatibility for a long list of worksheet
functions. I don't have a Blackberry (I discussed that with the
client; they were well aware that I would not have a Blackberry to
work on), so I made the spreadsheet in Excel.

For the client, it worked fine in Excel but on the Blackberries they
got a message that the file was read only.

It turns out Sheets To Go gives that error when it isn't compatible
with a formula (why not just return #N/A in the affected cells?
Weird.)

But I had been careful to only use the functions that their list
showed they were compatible with. We narrowed the problem down to
COUNTA with a long list of ranges. At first I thought Sheets To Go
might not like that many parameters, or more than one, but that wasn't
the problem.

It turned out that the problem was that one of the parameters in
COUNTA was this:

B16:B17:B19

Of course, that's not a valid reference, or at least that's been my
understanding for 20 years. But when I selected that parameter in the
formula bar in Excel and pressed F9, it showed that Excel was
evaluating it as if it were:

B16:B19

When we fixed that parameter, Davatiz Sheets To Go was fine with that
worksheet.

I was surprised that Excel would evaluate B16:B17:B19 as if it were
B16:B19, instead of returning an error. In this case it happened to
evaluate the intended cells, but if the cell references are further
apart, like this:

B12:B17:B19

It still uses the whole range between the outer cells, like this:

B12:B19

Which might not be what the user intended. For example, maybe the user
intended this:

B12,B17:B19

Personally I think it would be better for Excel to return an error
with an incorrect cell reference rather than, apparently, guess what
the user intended.

Greg