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?

On Mon, 6 Oct 2008 16:12:01 -0400, Jim Backus wrote
(in article <TpquPuPd0tCd-pn2-Wnou8fN5nysT at (no spam) localhost>):

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.

On Mon, 6 Oct 2008 16:12:01 -0400, Jim Backus wrote
(in article <TpquPuPd0tCd-pn2-Wnou8fN5nysT at (no spam) localhost>):

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.

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.