referencing multiple sheets and sum

If you want to use that formula construct, you'll need to create those missing sheets. With no data in them, they are harmless. And you can leave them in the correct order in the workbook, then hide them.

If you use the indirect method to reference a non-existent sheet, the formula will break.

I apologize if my answers were misleading implying alternate methods. 3D referencing across multiple sheets works in Excel exactly as you know it does, the only requirements is that you accurately name the first and the last sheet in the range to sum through. If you name a sheet that doesn't exist, either directly or indirectly, all the 3D formulas I know will break.

The simple solution you've dismissed out of hand. Create the sheets, hide them if you must. Problem goes away.

One way, you must have a column that contain a sheet name.

See the attachment

SDCh, when you delete the sheet 3-16 your formula breaks, too. The OP wants a formula that has a start and end date range depicting sheet name "range", but the actual sheets themselves may not exist, thus my point that all these tricks break in that scenario.

I admit that if the OP delete the name, it will break all the formula.

But from my point of view:
- The OP not have sheet that the day is holidays
- The formula that OP write always create a list of sheet that not exist

From that I suggest the OP has a list of sheet, if the OP want to delete or exclude some sheet so the OP must re-work the formula to be fit with the list.

Here's a possibility...

Assuming this has to work in Excel 2003 as your profile indicates.

Create this named formula:
Goto the menu Insert>Name>Define
Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())
OK out

Create a list of the sheet names. Enter this formula in F1:

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(F$1:F1))))

Copy down until you get blanks. If you will be adding new sheets in the future then you need to copy this formula down to enough cells that it will pickup the new sheet names as they are added. How many cells that is only you would know! For the purposes of this example let's assume you copy the formula down to F10.

Now, enter the sheet range in cells G1:H1 that you want to get the sum of. for example, to get sum across sheets 3-12 thru 3-16:

G1 = 3-12
H1 = 3-16

Then, to get the sum of cell A6 across the sheet range this formula entered in I1:

=SUMPRODUCT(N(INDIRECT("'"&INDEX(INDEX(F1:F10,MATCH(G1,F1:F10,0)):INDEX(F1:F10,MATCH(H1,F1:F10,0)),0)&"'!A6")))

The formula will account for sheet additions or deletions.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.