Autofill failure

Originally Posted by nemec7

I'm putting together an Excel workbook for some tryouts for a team and am having some problems. I have 81 total sheets, and on the first sheet I want to have a cell referenced for each of the following 80 sheets(all named 1, 2, 3, 4, and so on until 80), and not have to type them in everytime. In column A I have the numbers 1-80 (each number corresponding with a different participant). In column B is where I want all the cells to be referenced (B1= cell b22 in sheet 1, B2= cell b22 in sheet 2, and so on... Sheets 1-80 are all the same format, and cell b22 has the total score for each participant on each sheet) I'm trying to use the formula: ='1'!B22, and then use autofill, but it will not cooperate. Anyone have any ideas on how to fix this??

Hi,

try

=INDIRECT(ROW()&"!B22")

and formula fill that downwards to row 80

hth
---

Originally Posted by nemec7

Incredible. It works. How is it that it works though? Thanks a lot for all the help.

Hi,

yes, it works.

The Indirect makes up a formula from the details supplied, in this case a Row() number (1 through 80) for the Sheet name, and the rest of the formula (being !B22 )

Perhaps also useful to you would be

=INDIRECT("Sheet"&ROW()&"!"&CELL("address"))

to retrieve the data from Sheet1-this.cell etc.

hth
---