|
Generic Worksheet Names
Hi all, I hope someone would be able to help me with my question.
I have a workbook containing 5 worksheets: Main Sheet, July, June, May, April. In each of these worksheets I defined a couple of ranges using InsertNameDefine. These ranges are Range1 and Range2. In the Main Sheet I have a formula that refers to values located in the next adjacent 3 worksheets (July, June, May) as follows: = SUMPRODUCT(--(July!Range1 = whatever),--(July!Range2 = whatever)) + SUMPRODUCT(--(June!Range1 = wahetever),--(June!Range2= whatever)) + SUMPRODUCT(--(May!Range1 = whatever),--(May!Range2= whatever)) In the above formula, I want to refer to the 3 worksheets July, June and May by their generic name (i.e. Sheet2, Sheet3 and Sheet4) not by the name I assigned to them. The reason is that I have a macro code that enables users to add one new worksheet at the beginning of each month. So on August 1st, a new worksheet (called August) will be created after Main Sheet. Therefore, August will become Sheet2 and July will then become Sheet3 as it will be shifted one step to the right. And the formula in the Main Sheet should then refer to the next 3 adjacent worksheets regardless of their names, in this case August, July, June. Im using Excel 2003. Thanks in advance Tendresse |
Generic Worksheet Names
The "generic" name to which you refer is called the CodeName and is
accessible only via VBA code. There is no way in the Excel user interface to reference the CodeName. For example, Debug.Print Worksheets("My Sheet").CodeName To access the code name, you don't go through the Worksheet collection -- instead you access it directly. For example, suppose Sheet1 is named "My Sheet". The following lines are functionally equivalent Worksheets("My Sheet1").Range("A1").Value = 1234 Sheet1.Range("A1").Value =1234 The difference is that if the user changes the name of "My Sheet" to something else, the first line of code will fail while the second line of code will continue to work fine. Broadly speaking, you cannot change the code name of a sheet. That said, it can be done but is usually not a good idea to do so. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Tendresse" wrote in message ... Hi all, I hope someone would be able to help me with my question. I have a workbook containing 5 worksheets: Main Sheet, July, June, May, April. In each of these worksheets I defined a couple of ranges using InsertNameDefine. These ranges are Range1 and Range2. In the Main Sheet I have a formula that refers to values located in the next adjacent 3 worksheets (July, June, May) as follows: = SUMPRODUCT(--(July!Range1 = whatever),--(July!Range2 = whatever)) + SUMPRODUCT(--(June!Range1 = wahetever),--(June!Range2= whatever)) + SUMPRODUCT(--(May!Range1 = whatever),--(May!Range2= whatever)) In the above formula, I want to refer to the 3 worksheets July, June and May by their generic name (i.e. Sheet2, Sheet3 and Sheet4) not by the name I assigned to them. The reason is that I have a macro code that enables users to add one new worksheet at the beginning of each month. So on August 1st, a new worksheet (called August) will be created after Main Sheet. Therefore, August will become Sheet2 and July will then become Sheet3 as it will be shifted one step to the right. And the formula in the Main Sheet should then refer to the next 3 adjacent worksheets regardless of their names, in this case August, July, June. Im using Excel 2003. Thanks in advance Tendresse |
Generic Worksheet Names
Chip, thank you so much for your reply ... much appreciated ... So given it's
impossible to achieve what i want directly through Excel User Interface, i'm thinking of the following idea using VBA .. please let me know whether you think (or don't think) it's a step in the right direction: what if a write a code that puts the names of sheets 2, 3 and 4 in 3 cells: Range("A1").Value = sheets(2).Name Range("A2").Value = sheets(3).name Range("A3").Value =sheets(4).name is there a way then to use the values of cells A1, A2, A3 in my SUMPRODUCT formula? Personally, I don't feel very optimistic about this idea, but afterall i don't know everything in Excel, so who knows? maybe it can inspire someone with a solution. The other idea i'm thinking of .. but i have no clue where to start with it: Is it all possible to calculate the entire SUMPRODUCT formula in VBA? Once again, thanks heaps for your reply .. Best Regards Tendresse "Chip Pearson" wrote: The "generic" name to which you refer is called the CodeName and is accessible only via VBA code. There is no way in the Excel user interface to reference the CodeName. For example, Debug.Print Worksheets("My Sheet").CodeName To access the code name, you don't go through the Worksheet collection -- instead you access it directly. For example, suppose Sheet1 is named "My Sheet". The following lines are functionally equivalent Worksheets("My Sheet1").Range("A1").Value = 1234 Sheet1.Range("A1").Value =1234 The difference is that if the user changes the name of "My Sheet" to something else, the first line of code will fail while the second line of code will continue to work fine. Broadly speaking, you cannot change the code name of a sheet. That said, it can be done but is usually not a good idea to do so. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "Tendresse" wrote in message ... Hi all, I hope someone would be able to help me with my question. I have a workbook containing 5 worksheets: Main Sheet, July, June, May, April. In each of these worksheets I defined a couple of ranges using InsertNameDefine. These ranges are Range1 and Range2. In the Main Sheet I have a formula that refers to values located in the next adjacent 3 worksheets (July, June, May) as follows: = SUMPRODUCT(--(July!Range1 = whatever),--(July!Range2 = whatever)) + SUMPRODUCT(--(June!Range1 = wahetever),--(June!Range2= whatever)) + SUMPRODUCT(--(May!Range1 = whatever),--(May!Range2= whatever)) In the above formula, I want to refer to the 3 worksheets July, June and May by their generic name (i.e. Sheet2, Sheet3 and Sheet4) not by the name I assigned to them. The reason is that I have a macro code that enables users to add one new worksheet at the beginning of each month. So on August 1st, a new worksheet (called August) will be created after Main Sheet. Therefore, August will become Sheet2 and July will then become Sheet3 as it will be shifted one step to the right. And the formula in the Main Sheet should then refer to the next 3 adjacent worksheets regardless of their names, in this case August, July, June. Im using Excel 2003. Thanks in advance Tendresse |
| All times are GMT +1. The time now is 05:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com