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