refer to sequential sheets in a formula

[Guest]

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.

 

[Guest]

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.

 

[Guest]

Thanks Max! This works.

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.

---

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.

 

[Max]

Good to hear that. Thanks for feeding back.