Generic Worksheet Names

[Tendresse]

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
Insert>Name>Define. 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.

I’m using Excel 2003.
Thanks in advance
Tendresse

 

[Chip Pearson]

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]

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