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.