Referring to Sheet locations in a formula

[PCLIVE]

In VBA, sheets can be referred to by either name or location.
Example:
Sheets("Sheet2").Activate
or
Sheets(2).Activate

Is there a way to refer to sheet locations in a formula? For example, I'd
like to sum cell C1 on sheets 2 through 10 (the last sheet will be
determined in a cell on sheet1).

I'm thinking it will be an array, but I'm not sure about referring to sheet
locations in a formula.

Thanks In Advance.
Paul

 

[T. Valko]

Try this:

A1 = the last sheet number = 10

=SUMPRODUCT(SUMIF(INDIRECT("sheet"&ROW(INDIRECT("2:"&A1))&"!C1"),"<>"))

This will sum Sheet2:Sheet10!C1

Biff

 

[PCLIVE]

Thanks Biff,

The problem is that my sheets are not named "Sheet1", "Sheet2", etc. They
have unique names without a number following. That is why I'm looking for
away to have the formula to somehow reference the sheet location order
rather than the name. I'm thinking I'll have to use VBA to get the result
I'm looking for. But I figured I'd throw it out there.

 

[T. Valko]

List your sheet names in a range of cells, say, A2:A6
In cells B2:B6 enter a 1 for those sheets that you want to include in the
sum

Then:

=SUMPRODUCT(SUMIF(OFFSET(B2,ROW(B2:B6)-MIN(ROW(B2:B6)),),1,INDIRECT("'"&A2:A6&"'!C1")))

Eh, you might be better off using code!

Biff

 

[PCLIVE]

I understand that I can do that, but my sheet names are not all created yet,
so I was hoping that I could have something that would increment as I added
sheets. I think I'll use VBA to get the second and last sheet names. Then
I can reference it properly.