#1   Report Post  
David
 
Posts: n/a
Default 3D Ranges

I am looking for a very simple piece of code illustrating the format for a
"3D" range ie one that spans more than one worksheet. Can anyone help. the MS
help function is utterly useless. Thanks
  #2   Report Post  
Duke Carey
 
Posts: n/a
Default

Code? For VBA or for a formula?

In a formula it is:

=SUM(Sheet2:Sheet3!C1:C6)



"David" wrote:

I am looking for a very simple piece of code illustrating the format for a
"3D" range ie one that spans more than one worksheet. Can anyone help. the MS
help function is utterly useless. Thanks

  #3   Report Post  
David
 
Posts: n/a
Default

Actually i want to name the range and refer to it

"David" wrote:

I am looking for a very simple piece of code illustrating the format for a
"3D" range ie one that spans more than one worksheet. Can anyone help. the MS
help function is utterly useless. Thanks

  #4   Report Post  
David
 
Posts: n/a
Default

I was wanting to name a range in multiple worksheets and refer to the named
range

"David" wrote:

I am looking for a very simple piece of code illustrating the format for a
"3D" range ie one that spans more than one worksheet. Can anyone help. the MS
help function is utterly useless. Thanks

  #5   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

If I understand you correctly, you can't name a 3D range in Excel.

--

Vasant

"David" wrote in message
...
Actually i want to name the range and refer to it

"David" wrote:

I am looking for a very simple piece of code illustrating the format for

a
"3D" range ie one that spans more than one worksheet. Can anyone help.

the MS
help function is utterly useless. Thanks





  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

Vasant Nanavati wrote...
If I understand you correctly, you can't name a 3D range in Excel.

....

No?

I open a new workbook, enter {1,2;6,3;5,4} in Sheet1!A1:B3 and
{100,200;600,300;500,400} in Sheet2!A1:B3, create the defined name x
referring to Sheet1:Sheet2!$A$1:$B$3, and enter the following formula
in Sheet1!D1.

=SUM(x)

This formula returns 2121, as expected.

  #7   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

Hi Harlan:

It doesn't work for me (Excel 2002); what am I doing wrong?

Regards,

Vasant

"Harlan Grove" wrote in message
oups.com...
Vasant Nanavati wrote...
If I understand you correctly, you can't name a 3D range in Excel.

...

No?

I open a new workbook, enter {1,2;6,3;5,4} in Sheet1!A1:B3 and
{100,200;600,300;500,400} in Sheet2!A1:B3, create the defined name x
referring to Sheet1:Sheet2!$A$1:$B$3, and enter the following formula
in Sheet1!D1.

=SUM(x)

This formula returns 2121, as expected.



  #8   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

Further, Names("x") does not have a RefersToRange property, only a RefersTo
property.

--

Vasant


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Hi Harlan:

It doesn't work for me (Excel 2002); what am I doing wrong?

Regards,

Vasant

"Harlan Grove" wrote in message
oups.com...
Vasant Nanavati wrote...
If I understand you correctly, you can't name a 3D range in Excel.

...

No?

I open a new workbook, enter {1,2;6,3;5,4} in Sheet1!A1:B3 and
{100,200;600,300;500,400} in Sheet2!A1:B3, create the defined name x
referring to Sheet1:Sheet2!$A$1:$B$3, and enter the following formula
in Sheet1!D1.

=SUM(x)

This formula returns 2121, as expected.





  #9   Report Post  
Biff
 
Posts: n/a
Default

Works for me. Using Excel 2002 (XP)

Biff

"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Hi Harlan:

It doesn't work for me (Excel 2002); what am I doing wrong?

Regards,

Vasant

"Harlan Grove" wrote in message
oups.com...
Vasant Nanavati wrote...
If I understand you correctly, you can't name a 3D range in Excel.

...

No?

I open a new workbook, enter {1,2;6,3;5,4} in Sheet1!A1:B3 and
{100,200;600,300;500,400} in Sheet2!A1:B3, create the defined name x
referring to Sheet1:Sheet2!$A$1:$B$3, and enter the following formula
in Sheet1!D1.

=SUM(x)

This formula returns 2121, as expected.





  #10   Report Post  
Tushar Mehta
 
Posts: n/a
Default

Hi Vasant,

The easiest way is to let XL do the work. Rather than typing the
range, I used the mouse to select the 2 sheets then the range. XL put
a single quote around the sheet1:sheet2 part.

I suspect 3D ranges are a kludge that MS cobbled together in response
to what it must have perceived as a competitive threat from one of the
other spreadsheet packages. It's a kludge since a 3D range violates
the XL object model. A range's parent is a worksheet. But, that,
obviously, is not true for a 3D range. So, it kinda works where it
works. I cannot recall MS making any enhancements to the 3D capability
since it was first introduced, which shouldn't really be a surprise
since it would require redoing a core component of the OM.

Further, it should not come as a surprise that it doesn't play well
with VBA. For example, with a single-sheet named range, the following
works just fine:
?application.worksheetfunction.Sum(range(activewor kbook.Names(2).Name))

But, replace Names(2) with Names(1) and the result is 'Application-
defined or object-defined error.' Yes, Names(1) is the 3D range and
Names(2) is a single-sheet name.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , "Vasant Nanavati"
<vasantn *AT* aol *DOT* com says...
Hi Harlan:

It doesn't work for me (Excel 2002); what am I doing wrong?

Regards,

Vasant

"Harlan Grove" wrote in message
oups.com...
Vasant Nanavati wrote...
If I understand you correctly, you can't name a 3D range in Excel.

...

No?

I open a new workbook, enter {1,2;6,3;5,4} in Sheet1!A1:B3 and
{100,200;600,300;500,400} in Sheet2!A1:B3, create the defined name x
referring to Sheet1:Sheet2!$A$1:$B$3, and enter the following formula
in Sheet1!D1.

=SUM(x)

This formula returns 2121, as expected.






  #11   Report Post  
Tushar Mehta
 
Posts: n/a
Default

If you mean VBA code, turn on the macro recorder (Tools | Macro
record new macro...) do whatever it is you want through the GUI, turn
off the recorder, and switch to the VBE. In most cases, XL will give
you the necessary code.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I am looking for a very simple piece of code illustrating the format for a
"3D" range ie one that spans more than one worksheet. Can anyone help. the MS
help function is utterly useless. Thanks

  #12   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Tushar Mehta" wrote...
....
I suspect 3D ranges are a kludge that MS cobbled together in response
to what it must have perceived as a competitive threat from one of the
other spreadsheet packages. It's a kludge since a 3D range violates
the XL object model. A range's parent is a worksheet. But, that,
obviously, is not true for a 3D range. So, it kinda works where it
works. I cannot recall MS making any enhancements to the 3D capability
since it was first introduced, which shouldn't really be a surprise
since it would require redoing a core component of the OM.

....

They're kludges, but they aren't ranges. IIRC, they debuted in Excel 4, and
they're not much more than syntactic sugar providing formula means to
accomplish Data consolidate across worksheets.

Online help is careful to refer to them only as 3D *REFERENCES*, not ranges,
and they don't function at all in VBA or the Excel object model, so nothing
violated.

There's nothing magic about making worksheets the parent class of the range
class. The workbook class could just as easily be the parent for the range
class (as is the case in Lotus 123's 32-bit object model). It would require
a thorough rewrite to change this, but Excel desperately needs such a
rewrite.


  #13   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

"Harlan Grove" wrote in message
...
They're kludges, but they aren't ranges. IIRC, they debuted in Excel 4,

and
they're not much more than syntactic sugar providing formula means to
accomplish Data consolidate across worksheets.


I guess that's what I was trying to convey when I said you couldn't name a
3D range. The solution you provided seems more akin to a named formula than
a named range. If I refer to Range("x") in code, VBA doesn't know what I'm
talking about.

Regards,

Vasant


  #14   Report Post  
David
 
Posts: n/a
Default

Tushar - what does the code in the name box actually look like. I can't get
it to work. Just 1 example will do. Thanks

"Tushar Mehta" wrote:

Hi Vasant,

The easiest way is to let XL do the work. Rather than typing the
range, I used the mouse to select the 2 sheets then the range. XL put
a single quote around the sheet1:sheet2 part.

I suspect 3D ranges are a kludge that MS cobbled together in response
to what it must have perceived as a competitive threat from one of the
other spreadsheet packages. It's a kludge since a 3D range violates
the XL object model. A range's parent is a worksheet. But, that,
obviously, is not true for a 3D range. So, it kinda works where it
works. I cannot recall MS making any enhancements to the 3D capability
since it was first introduced, which shouldn't really be a surprise
since it would require redoing a core component of the OM.

Further, it should not come as a surprise that it doesn't play well
with VBA. For example, with a single-sheet named range, the following
works just fine:
?application.worksheetfunction.Sum(range(activewor kbook.Names(2).Name))

But, replace Names(2) with Names(1) and the result is 'Application-
defined or object-defined error.' Yes, Names(1) is the 3D range and
Names(2) is a single-sheet name.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , "Vasant Nanavati"
<vasantn *AT* aol *DOT* com says...
Hi Harlan:

It doesn't work for me (Excel 2002); what am I doing wrong?

Regards,

Vasant

"Harlan Grove" wrote in message
oups.com...
Vasant Nanavati wrote...
If I understand you correctly, you can't name a 3D range in Excel.
...

No?

I open a new workbook, enter {1,2;6,3;5,4} in Sheet1!A1:B3 and
{100,200;600,300;500,400} in Sheet2!A1:B3, create the defined name x
referring to Sheet1:Sheet2!$A$1:$B$3, and enter the following formula
in Sheet1!D1.

=SUM(x)

This formula returns 2121, as expected.





  #15   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

"Tushar Mehta" wrote in message
om...
The easiest way is to let XL do the work. Rather than typing the
range, I used the mouse to select the 2 sheets then the range. XL put
a single quote around the sheet1:sheet2 part.


Hi Tushar:

I think I'm brain-dead this week ... I couldn't get this to work either. If
I select both sheets and then select a range and name it, the name seems to
apply only to the selected range on the active sheet. I tried doing this
with the Name Box as well as with the Define Name dialog.

Regards,

Vasant




  #16   Report Post  
David
 
Posts: n/a
Default

Actually Tushar I am referring to the code or reference that appears in the
"Define Name" dialogue box under Insert/Name/Define menu

"Tushar Mehta" wrote:

If you mean VBA code, turn on the macro recorder (Tools | Macro
record new macro...) do whatever it is you want through the GUI, turn
off the recorder, and switch to the VBE. In most cases, XL will give
you the necessary code.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I am looking for a very simple piece of code illustrating the format for a
"3D" range ie one that spans more than one worksheet. Can anyone help. the MS
help function is utterly useless. Thanks


  #17   Report Post  
Tushar Mehta
 
Posts: n/a
Default

Similar to Harlan's first post in this discussion:
=Sheet1:Sheet2!$A$1:$D$1

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Tushar - what does the code in the name box actually look like. I can't get
it to work. Just 1 example will do. Thanks

"Tushar Mehta" wrote:

Hi Vasant,

The easiest way is to let XL do the work. Rather than typing the
range, I used the mouse to select the 2 sheets then the range. XL put
a single quote around the sheet1:sheet2 part.

I suspect 3D ranges are a kludge that MS cobbled together in response
to what it must have perceived as a competitive threat from one of the
other spreadsheet packages. It's a kludge since a 3D range violates
the XL object model. A range's parent is a worksheet. But, that,
obviously, is not true for a 3D range. So, it kinda works where it
works. I cannot recall MS making any enhancements to the 3D capability
since it was first introduced, which shouldn't really be a surprise
since it would require redoing a core component of the OM.

Further, it should not come as a surprise that it doesn't play well
with VBA. For example, with a single-sheet named range, the following
works just fine:
?application.worksheetfunction.Sum(range(activewor kbook.Names(2).Name))

But, replace Names(2) with Names(1) and the result is 'Application-
defined or object-defined error.' Yes, Names(1) is the 3D range and
Names(2) is a single-sheet name.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , "Vasant Nanavati"
<vasantn *AT* aol *DOT* com says...
Hi Harlan:

It doesn't work for me (Excel 2002); what am I doing wrong?

Regards,

Vasant

"Harlan Grove" wrote in message
oups.com...
Vasant Nanavati wrote...
If I understand you correctly, you can't name a 3D range in Excel.
...

No?

I open a new workbook, enter {1,2;6,3;5,4} in Sheet1!A1:B3 and
{100,200;600,300;500,400} in Sheet2!A1:B3, create the defined name x
referring to Sheet1:Sheet2!$A$1:$B$3, and enter the following formula
in Sheet1!D1.

=SUM(x)

This formula returns 2121, as expected.






  #18   Report Post  
Tushar Mehta
 
Posts: n/a
Default

I invariably forget to use the Name Box since as often as not I'm
creating named formulas.

The way I did this was to get to the Define name dialog box, enter a
name, tab to the 'refers to' field then use the mouse to select sheet1,
SHIFT+sheet2 tab. At this point the 'refers to' field contains
='Sheet1:Sheet2'!

Next, click on A1 and drag-extend the selection to D1. The 'refers
to' field contains ='Sheet1:Sheet2'!$A$1:$D$1 Back in the dialog box,
click OK.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , "Vasant Nanavati"
<vasantn *AT* aol *DOT* com says...
"Tushar Mehta" wrote in message
om...
The easiest way is to let XL do the work. Rather than typing the
range, I used the mouse to select the 2 sheets then the range. XL put
a single quote around the sheet1:sheet2 part.


Hi Tushar:

I think I'm brain-dead this week ... I couldn't get this to work either. If
I select both sheets and then select a range and name it, the name seems to
apply only to the selected range on the active sheet. I tried doing this
with the Name Box as well as with the Define Name dialog.

Regards,

Vasant



  #19   Report Post  
Vasant Nanavati
 
Posts: n/a
Default

Hi Tushar:

Interesting ... I had tried to do it using the Ctrl key and it didn't work.
But of course, the Shift key makes more sense for a 3D range, even for 2
sheets.

Regards,

Vasant



"Tushar Mehta" wrote in message
om...
I invariably forget to use the Name Box since as often as not I'm
creating named formulas.

The way I did this was to get to the Define name dialog box, enter a
name, tab to the 'refers to' field then use the mouse to select sheet1,
SHIFT+sheet2 tab. At this point the 'refers to' field contains
='Sheet1:Sheet2'!

Next, click on A1 and drag-extend the selection to D1. The 'refers
to' field contains ='Sheet1:Sheet2'!$A$1:$D$1 Back in the dialog box,
click OK.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , "Vasant Nanavati"
<vasantn *AT* aol *DOT* com says...
"Tushar Mehta" wrote in

message
om...
The easiest way is to let XL do the work. Rather than typing the
range, I used the mouse to select the 2 sheets then the range. XL put
a single quote around the sheet1:sheet2 part.


Hi Tushar:

I think I'm brain-dead this week ... I couldn't get this to work either.

If
I select both sheets and then select a range and name it, the name seems

to
apply only to the selected range on the active sheet. I tried doing this
with the Name Box as well as with the Define Name dialog.

Regards,

Vasant





  #20   Report Post  
David
 
Posts: n/a
Default


Tushar - now I have the range names and it works for the sum function but I
do not seem to be able to reference teh range with V/HLOOKUP which is really
why I want the extra spreadhseets in the range (needless to say I have price
tables that don't fit on one sheet). Any thoughts as to how to resolve this.
Thanks David

"David" wrote:

I am looking for a very simple piece of code illustrating the format for a
"3D" range ie one that spans more than one worksheet. Can anyone help. the MS
help function is utterly useless. Thanks



  #21   Report Post  
Tushar Mehta
 
Posts: n/a
Default

I have some unfortunate news for you. Searching the "utterly useless"
help for the keywords '3d range' (w/o the quotes) leads to the topic
'Refer to the same cell or range on multiple sheets,' which contains
the section 'Functions that can be used in a 3-D reference'. Neither
VLOOKUP nor HLOOKUP is listed in it.

That said...

Who do you work for? A legacy airline? What do you do that requires a
price list with more than 65536*256 entries? If nothing else,
maintaining that with any degree of accuracy must be a nightmare. ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Tushar - now I have the range names and it works for the sum function but I
do not seem to be able to reference teh range with V/HLOOKUP which is really
why I want the extra spreadhseets in the range (needless to say I have price
tables that don't fit on one sheet). Any thoughts as to how to resolve this.
Thanks David

"David" wrote:

I am looking for a very simple piece of code illustrating the format for a
"3D" range ie one that spans more than one worksheet. Can anyone help. the MS
help function is utterly useless. Thanks


  #22   Report Post  
David
 
Posts: n/a
Default

Actually I work for a fund management company and we mainatin historical
daily prices on the traded stocks we monitor. The prices are used for back
testing as well as generating technical signals for current trades - MACD
etc. Excel constrains us to 256 days of history if we stack the stock
horizontally or 256 stocks if we stack the stocks in columns. My thought was
that one could create and enlarged named range crossing 2-3 sheets allowing
me to run price history on the several hundred stocks we cover. From what you
are saying this may not be possible. Do you have any ideas?

"David" wrote:

I am looking for a very simple piece of code illustrating the format for a
"3D" range ie one that spans more than one worksheet. Can anyone help. the MS
help function is utterly useless. Thanks

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Ranges for a Chart Barb R. Charts and Charting in Excel 0 May 31st 05 11:52 PM
Like 123, allow named ranges, and print named ranges WP Excel Discussion (Misc queries) 1 April 8th 05 06:07 PM
Problem with graph ranges No Such Luck Charts and Charting in Excel 6 December 3rd 04 02:09 PM
compare unique identifiers in multiple ranges bawilli_91125 Charts and Charting in Excel 1 November 30th 04 07:34 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 09:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"