3D Named Range

[Guest]

I ran into this problem in 2000 and had to use LOTUS instead. I figured that
it had been fixed in 2003, but it appears that I'm wrong.

I create a 3D Named Range, per the Help File, and several Online Sites,
which say exactly the same thing, but it doesn't work for me. It seems as
though it's been created, because it shows up in the Define Name Window, but
it doesn't show up in the Named Range List on the Worksheet. Nor can you
reference it in a Macro.

The Named Range as it is shown in the Define Name Window:

=sch_jnt_acct:sch_atd_last!$f$2:$f$100

Any assistance would be greatly appreciated, as I don't want to go back to
LOTUS.

 

[Harlan Grove]

I ran into this problem in 2000 and had to use LOTUS instead. I
figured that it had been fixed in 2003, but it appears that I'm
wrong.

Yup. Microsoft seems to have no intention of making Excel a true 3D
spreadsheet.

I create a 3D Named Range, per the Help File, and several Online
Sites, which say exactly the same thing, but it doesn't work for
me. It seems as though it's been created, because it shows up in
the Define Name Window, but it doesn't show up in the Named Range
List on the Worksheet. Nor can you reference it in a Macro.

....

If I put the following in Sheet1!A1:C3

111 112 113
121 122 123
131 132 133

and the following in Sheet2!A1C3

211 212 213
221 222 223
231 232 233

then define the name foo referring to =Sheet1:Sheet2!$A$1:$C$3, I can
use that name wherever I can use 3D references (not many places),
e.g., =SUM(foo) returns 3096. And foo shows up in the Paste Names
dialog on my system. You're correct that it won't appear in the Named
Range List.

Any assistance would be greatly appreciated, as I don't want to go
back to LOTUS.

What are you trying to do with these 3D ranges? There are work arounds
for most things, but you need to provide more details.

 

[Guest]

Then, do you know why in their documentation that explain how to create a 3d
named range, and even go so far as to say that if you insert sheets between
the first and last sheets that it will automatically be included in the range?

How can they hope to sell something that's so inferior to their competition?
Lotus has been doing this for 30 years. Amazing!

Thanks for the info!

TomD

 

[Harlan Grove]

Then, do you know why in their documentation that explain how to
create a 3d named range, and even go so far as to say that if you
insert sheets between the first and last sheets that it will
automatically be included in the range?

They provide the feature because it could be useful on the FEW
occasions it could be used. They can be used in the aggregation
functions - COUNT[A], SUM, AVERAGE[A], MIN[A], MAX[A], VAR[PA],
STDEV[PA], and (most interestingly) NPV, but that's all.

How can they hope to sell something that's so inferior to their
competition?

What competition? When's the last time you saw Lotus SmartSuite in a
computer store? While you may see WordPerfect Office, Quattro Pro is
now somewhat unstable. As for OpenOffice/StarOffice, they follow
Excel's lead for the most part in terms of minimal 3D functionality.

Lotus has been doing this for 30 years. Amazing!

Wrong. Lotus added the 3rd dimension to 123 in 1989 with Release 3.0,
so not even 20 years yet.

The market (distorted at every opportunity Microsoft could get away
with) has spoken. And realize that Lotus never lost an opportunity to
shoot itself in the foot. They fought the Look & Feel lawsuits to
protect a doomed character mode interface, willfully misjudged the
impact of Windows, embarrassed themselves with 123 For Windows Release
1 (far & away the worst 123 release, and proof that Lotus in 1991 just
didn't understand Windows program design), and it took Lotus 4 years
from Excel 5 with VBA to produce 123 97 Edition with LotusScript, and
LotusScript is garbage compared to VBA.

But I agree that 123 did a much better job with functions and 3D range
references.

 

[Guest]

Hi Harlan,

Thanks for the reply. I can’t argue with you on VBA because LOTUS Script
leaves a lot to be desired. In fact I only tried to write a script once, and
quickly abandoned it. And as for the character mode interface, that too has
been a thorn in my side, but I’ve patiently worked around it.

I’m sure that you’re more knowledgeable than I am on when LOTUS introduced
3D, but I did Corporate Financial Spreadsheets for McDonnell Douglas and EDS
from the early 80s through the mid 90s, and I don’t remember when it wasn’t
there. At any rate, Named Ranges of all natures are very useful and powerful
tools. They save more programming time than anything I can think of.

As we both know, you can create the Named Range, it’s just that it doesn’t
appear in the Named Range List. You mentioned several functions that it’ll
work with, and I’ve seen a similar list in some of the other Forums, but,
since you can’t select it from the Worksheet, then I guess that it’s only
available via VBA. Or, will the function address it as well?

I agree that you don’t see LOTUS in your local Computer Store, anymore, but
I think there’s a logical reason for that. First of all, it doesn’t come
packaged with a suite of products, like Office, which is often already on you
new computer. Secondly, it’s very expensive, because it’s so powerful and
robust, and therefore, it’s really more suitable for businesses. EXCEL
provides more than ample functionality for the typical small business or home
use.

Interestingly enough, there was a mention on CNBC, a few months ago, about
how there was a renewed interest in LOTUS. True or not, it looks like I’m
stuck with a relic that works for me.

Thanks again for your help and information and I’ll be waiting for the
notice from MS, that their 3D function works!

TomD

Then, do you know why in their documentation that explain how to
create a 3d named range, and even go so far as to say that if you
insert sheets between the first and last sheets that it will
automatically be included in the range?

They provide the feature because it could be useful on the FEW
occasions it could be used. They can be used in the aggregation
functions - COUNT[A], SUM, AVERAGE[A], MIN[A], MAX[A], VAR[PA],
STDEV[PA], and (most interestingly) NPV, but that's all.

How can they hope to sell something that's so inferior to their
competition?

What competition? When's the last time you saw Lotus SmartSuite in a
computer store? While you may see WordPerfect Office, Quattro Pro is
now somewhat unstable. As for OpenOffice/StarOffice, they follow
Excel's lead for the most part in terms of minimal 3D functionality.

Lotus has been doing this for 30 years. Amazing!

Wrong. Lotus added the 3rd dimension to 123 in 1989 with Release 3.0,
so not even 20 years yet.

The market (distorted at every opportunity Microsoft could get away
with) has spoken. And realize that Lotus never lost an opportunity to
shoot itself in the foot. They fought the Look & Feel lawsuits to
protect a doomed character mode interface, willfully misjudged the
impact of Windows, embarrassed themselves with 123 For Windows Release
1 (far & away the worst 123 release, and proof that Lotus in 1991 just
didn't understand Windows program design), and it took Lotus 4 years
from Excel 5 with VBA to produce 123 97 Edition with LotusScript, and
LotusScript is garbage compared to VBA.

But I agree that 123 did a much better job with functions and 3D range
references.