|One way would be to put this in A1 and drag down:|
The INDIRECT function looks at the text inside the parenthesis and uses it as a cell reference. The Row() function will pick up the Row number for current Row as you drag it down.
If you aren't starting in Row 1, but need the first formula to reference Sheet1, just do some simple math. Let's say you are starting in Row 5:
will also reference Sheet1.
The main drawback of this method is that when you (or worse yet, somebody else) looks at this formula next year, they're going to have to think about what it's doing since it's not as simple as =Sheet1!B7, but returns the same result.
Another option is a macro...
To fill Column A:
For NxtRow = 1 To 500
Cells(NxtRow, 1).Formula = "=Sheet" & NxtRow & "!B7"
This will put the simple formula (e.g. =Sheet1!B7) in the cells so that there will be no confusion next year.