Referring to Sheet locations in a formula
 

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

Referring to Sheet locations in a formula
 

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" <pclive(remove wrote in message
...
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

Referring to Sheet locations in a formula
 

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" wrote in message
...
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" <pclive(remove wrote in message
...
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

Referring to Sheet locations in a formula
 

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" <pclive(remove wrote in message
...
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" wrote in message
...
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" <pclive(remove wrote in message
...
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

Referring to Sheet locations in a formula
 

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.


"T. Valko" wrote in message
...
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" <pclive(remove wrote in message
...
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" wrote in message
...
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" <pclive(remove wrote in message
...
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