If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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 |
#18
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problem with graph ranges | No Such Luck | Charts and Charting | 6 | December 3rd, 2004 01:09 PM |
Named Ranges (transfer to a new workbook) | Dennis | General Discussion | 1 | October 25th, 2004 07:57 PM |
Chart Data Ranges | Joyce | Charts and Charting | 2 | October 8th, 2004 09:54 PM |
Import changes named ranges | ICE9 | General Discussion | 3 | August 25th, 2004 01:49 AM |