refer to sequential sheets in a formula
 

I have 100 sheets in a workbook, with each sheet named "1" to "100"
sequentially. In a summary sheet, I want to include a formula that refers to
a range of cells in each sheet separately (i.e. each sheet sequentially
rather than all sheets together).

I have created a countif formula in cell A1 on my summary sheet that counts
the number of entries in a defined range on sheet 1. I then want to pull down
this formula such that Cell A2 counts from the same defined range in sheet 2
and so on. That is, I want the range of cells to stay the same, but the sheet
name to change (to the next sheet in the workbook) when I drag down the
formula. Any pointers would be helpful.

refer to sequential sheets in a formula
 

Suppose you had this in A1:
=COUNTIF('1'!A1:A5,"=5")

Replace the above with this instead, in A1:
=COUNTIF(INDIRECT("'"&ROW(A1)&"'!A1:A5"),"=5")
Then just copy A1 down 100 rows to A100 to extract the required results from
each of the 100 sheets named as: 1,2,3, ... 100

Note that the same formula above [ie with ROW(A1)] should be used in any
starting cell, if it's other than A1. ROW(A1) is the incrementer part within
the formula. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"seanoniallain" wrote:
I have 100 sheets in a workbook, with each sheet named "1" to "100"
sequentially. In a summary sheet, I want to include a formula that refers to
a range of cells in each sheet separately (i.e. each sheet sequentially
rather than all sheets together).

I have created a countif formula in cell A1 on my summary sheet that counts
the number of entries in a defined range on sheet 1. I then want to pull down
this formula such that Cell A2 counts from the same defined range in sheet 2
and so on. That is, I want the range of cells to stay the same, but the sheet
name to change (to the next sheet in the workbook) when I drag down the
formula. Any pointers would be helpful.

refer to sequential sheets in a formula
 

Thanks Max! This works.

"Max" wrote:

Suppose you had this in A1:
=COUNTIF('1'!A1:A5,"=5")

Replace the above with this instead, in A1:
=COUNTIF(INDIRECT("'"&ROW(A1)&"'!A1:A5"),"=5")
Then just copy A1 down 100 rows to A100 to extract the required results from
each of the 100 sheets named as: 1,2,3, ... 100

Note that the same formula above [ie with ROW(A1)] should be used in any
starting cell, if it's other than A1. ROW(A1) is the incrementer part within
the formula. Adapt to suit.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"seanoniallain" wrote:
I have 100 sheets in a workbook, with each sheet named "1" to "100"
sequentially. In a summary sheet, I want to include a formula that refers to
a range of cells in each sheet separately (i.e. each sheet sequentially
rather than all sheets together).

I have created a countif formula in cell A1 on my summary sheet that counts
the number of entries in a defined range on sheet 1. I then want to pull down
this formula such that Cell A2 counts from the same defined range in sheet 2
and so on. That is, I want the range of cells to stay the same, but the sheet
name to change (to the next sheet in the workbook) when I drag down the
formula. Any pointers would be helpful.

refer to sequential sheets in a formula
 

Good to hear that. Thanks for feeding back.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"seanoniallain" wrote in message
...
Thanks Max! This works.