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
|