123 feature not supported in Excel & NeoOffice?

[Database Administrator]

All,

I've been a long time user of Smartsuite on OS/2 and Windows but as
there has been little development for quite a while and I've now got a
Mac, my spreadsheets are being transferred via Excel format to
NeoOffice's Calc (an Open Office clone). One of my spreadsheets
consists of many sheets each recording daily rainfall for a year. The
final sheet has a summary of the monthly statistics. In 123 I use
formulae to count how many years have complete records for the month.
The formula is something like:

+12-Countif(sheet1.b36:sheet12.b36,"y")

where cell B36 on each sheet has a "y" or "n" indicating whether the
data is complete or not. This formula works as expected in 123, but
NeoOffice Calc thows a "504" error and IIRC Excel does the same.
Buried deep in Excel's help there is an explanation that ranges cannot
span sheets, but NeoOffice didn't admit any such limitation.

Two questions:

Is is true that most spreadsheets do not support ranges spanning
sheets?

Can someone suggest a work around?

If there isn't one, I'll go back to Smartsuite & 123.

TIA

Jim
--
Jim Backus running OS/2 Warp 3 & 4, OS-X and Win98SE
bona fide replies to j backus jita
demon co uk

[Database Administrator]

On Mon, 6 Oct 2008 16:12:01 -0400, Jim Backus wrote
(in article ):

> All,
>
> I've been a long time user of Smartsuite on OS/2 and Windows but as
> there has been little development for quite a while and I've now got a
> Mac, my spreadsheets are being transferred via Excel format to
> NeoOffice's Calc (an Open Office clone). One of my spreadsheets
> consists of many sheets each recording daily rainfall for a year. The
> final sheet has a summary of the monthly statistics. In 123 I use
> formulae to count how many years have complete records for the month.
> The formula is something like:
>
> +12-Countif(sheet1.b36:sheet12.b36,"y")

For NeoOffice you want a ";" where you have a ",".
>
> where cell B36 on each sheet has a "y" or "n" indicating whether the
> data is complete or not. This formula works as expected in 123, but
> NeoOffice Calc thows a "504" error and IIRC Excel does the same.
> Buried deep in Excel's help there is an explanation that ranges cannot
> span sheets, but NeoOffice didn't admit any such limitation.
>
> Two questions:
>
> Is is true that most spreadsheets do not support ranges spanning
> sheets?

NeoOffice can certainly handle formulas that take values from multiple
sheets. I've never tried to specify a range that spans sheets, but
just ran a test of "=SUM(Sheet1.A1:Sheet3.A1)" and it worked.

I also tested "=COUNTIF(Sheet1.A1:Sheet3.A1;"y")" and it fails with a
504 error, which means "Function parameter is not valid, for example,
text instead of a number, or a domain reference instead of cell
reference."

An alternative would be to have your test put a 1 instead of a y in
each test cell. Then you could use SUM (which works across sheets)
instead of COUNTIF (which doesn't).

If for some reason you must use a "y" you may be out of luck, unless
you want to do a whole mess of IFs. I think I would do that rather
than run 123. What would be your plan? Run eCS under Parallels? I've
thought of doing that for ProNews, but never for Smartsuite.

--
John Varela
Trade NEW lamps for OLD for email.

[Database Administrator]

On Wed, 08 Oct 2008 20:55:11 GMT, John Varela wrote:

> On Mon, 6 Oct 2008 16:12:01 -0400, Jim Backus wrote
> (in article ):
>
>> All,
>>
>> I've been a long time user of Smartsuite on OS/2 and Windows but as
>> there has been little development for quite a while and I've now got a
>> Mac, my spreadsheets are being transferred via Excel format to
>> NeoOffice's Calc (an Open Office clone). One of my spreadsheets
>> consists of many sheets each recording daily rainfall for a year. The
>> final sheet has a summary of the monthly statistics. In 123 I use
>> formulae to count how many years have complete records for the month.
>> The formula is something like:
>>
>> +12-Countif(sheet1.b36:sheet12.b36,"y")
>
> For NeoOffice you want a ";" where you have a ",".
>>
>> where cell B36 on each sheet has a "y" or "n" indicating whether the
>> data is complete or not. This formula works as expected in 123, but
>> NeoOffice Calc thows a "504" error and IIRC Excel does the same.
>> Buried deep in Excel's help there is an explanation that ranges cannot
>> span sheets, but NeoOffice didn't admit any such limitation.
>>
>> Two questions:
>>
>> Is is true that most spreadsheets do not support ranges spanning
>> sheets?
>
> NeoOffice can certainly handle formulas that take values from multiple
> sheets. I've never tried to specify a range that spans sheets, but
> just ran a test of "=SUM(Sheet1.A1:Sheet3.A1)" and it worked.
>
> I also tested "=COUNTIF(Sheet1.A1:Sheet3.A1;"y")" and it fails with a
> 504 error, which means "Function parameter is not valid, for example,
> text instead of a number, or a domain reference instead of cell
> reference."
>
> An alternative would be to have your test put a 1 instead of a y in
> each test cell. Then you could use SUM (which works across sheets)
> instead of COUNTIF (which doesn't).
>
> If for some reason you must use a "y" you may be out of luck, unless
> you want to do a whole mess of IFs. I think I would do that rather
> than run 123. What would be your plan? Run eCS under Parallels? I've
> thought of doing that for ProNews, but never for Smartsuite.

When doing COUNTIFs across sheets, I just do a COUNTIF on each sheet, then
sum the separate COUNTIFs on each sheet.

[Database Administrator]

On Wed, 8 Oct 2008 20:55:11 UTC, John Varela
wrote:

> On Mon, 6 Oct 2008 16:12:01 -0400, Jim Backus wrote
> (in article ):
>
> > All,
> >
> > I've been a long time user of Smartsuite on OS/2 and Windows but as
> > there has been little development for quite a while and I've now got a
> > Mac, my spreadsheets are being transferred via Excel format to
> > NeoOffice's Calc (an Open Office clone). One of my spreadsheets
> > consists of many sheets each recording daily rainfall for a year. The
> > final sheet has a summary of the monthly statistics. In 123 I use
> > formulae to count how many years have complete records for the month.
> > The formula is something like:
> >
> > +12-Countif(sheet1.b36:sheet12.b36,"y")
>
> For NeoOffice you want a ";" where you have a ",".

I'll check that. Obviously the message was posted from and OS/2 PC not
the Mac, so I was unable to cut and paste.

> >
> > where cell B36 on each sheet has a "y" or "n" indicating whether the
> > data is complete or not. This formula works as expected in 123, but
> > NeoOffice Calc thows a "504" error and IIRC Excel does the same.
> > Buried deep in Excel's help there is an explanation that ranges cannot
> > span sheets, but NeoOffice didn't admit any such limitation.
> >
> > Two questions:
> >
> > Is is true that most spreadsheets do not support ranges spanning
> > sheets?
>
> NeoOffice can certainly handle formulas that take values from multiple
> sheets. I've never tried to specify a range that spans sheets, but
> just ran a test of "=SUM(Sheet1.A1:Sheet3.A1)" and it worked.
>
> I also tested "=COUNTIF(Sheet1.A1:Sheet3.A1;"y")" and it fails with a
> 504 error, which means "Function parameter is not valid, for example,
> text instead of a number, or a domain reference instead of cell
> reference."
>
> An alternative would be to have your test put a 1 instead of a y in
> each test cell. Then you could use SUM (which works across sheets)
> instead of COUNTIF (which doesn't).
>
> If for some reason you must use a "y" you may be out of luck, unless
> you want to do a whole mess of IFs. I think I would do that rather
> than run 123. What would be your plan? Run eCS under Parallels? I've
> thought of doing that for ProNews, but never for Smartsuite.
>

Thanks for the suggestion. If SUM works that would do the trick.

I'd hoped someone might have had experience of the new Smartsuite,
which is an Open Office clone from IBM / Lotus. One would expect it to
support all 123 functionality. The new Smartsuite is promised for Mac
but I don't think there is even a beta out yet.

Until recently I ran 123 under OS/2 but something has broken and I
haven't fixed it yet. I also have Smartsuite for Windows running on
Win 98.

--
Jim Backus running OS/2 Warp 3 & 4, Debian Linux and Win98SE
bona fide replies to j backus jita
demon co uk

[Database Administrator]

On Thu, 9 Oct 2008 14:30:35 UTC, salgud
wrote:

> > An alternative would be to have your test put a 1 instead of a y in
> > each test cell. Then you could use SUM (which works across sheets)
> > instead of COUNTIF (which doesn't).
> >
> > If for some reason you must use a "y" you may be out of luck, unless
> > you want to do a whole mess of IFs. I think I would do that rather
> > than run 123. What would be your plan? Run eCS under Parallels? I've
> > thought of doing that for ProNews, but never for Smartsuite.
>
> When doing COUNTIFs across sheets, I just do a COUNTIF on each sheet, then
> sum the separate COUNTIFs on each sheet.
>

My spreadsheet has a column for each month of the year; I want a month
by month summary, so using COUNTIF on each sheet wouldn't help.

John's suggestion of using 1 instead of y and 0 or blank instead of n
is what I'll try.

Thanks to both for your time.

--
Jim Backus running OS/2 Warp 3 & 4, Debian Linux and Win98SE
bona fide replies to j backus jita
demon co uk