Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
3D Named Range
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
3D Named Range
TomD wrote...
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
3D Named Range
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" wrote: TomD wrote... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
3D Named Range
TomD wrote...
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
3D Named Range
Hi Harlan,
Thanks for the reply. I cant 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 Ive patiently worked around it. Im sure that youre 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 dont remember when it wasnt 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, its just that it doesnt appear in the Named Range List. You mentioned several functions that itll work with, and Ive seen a similar list in some of the other Forums, but, since you cant select it from the Worksheet, then I guess that its only available via VBA. Or, will the function address it as well? I agree that you dont see LOTUS in your local Computer Store, anymore, but I think theres a logical reason for that. First of all, it doesnt come packaged with a suite of products, like Office, which is often already on you new computer. Secondly, its very expensive, because its so powerful and robust, and therefore, its 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 Im stuck with a relic that works for me. Thanks again for your help and information and Ill be waiting for the notice from MS, that their 3D function works! TomD "Harlan Grove" wrote: TomD wrote... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) |