Changing Sheet names with Columns (Excel)

[Ian Curtis]

I don't have a whole lot of experience with Excel, and the built in help is pretty unhelpful, so forgive me if I'm missing something silly.

So, lets say I have a work sheet (let's say Sheet1), and in cell A1 (on Sheet1) I have "=Sheet2!$A$2" (minus the quotes, obviously). If I were to drag this cell A1 across the row, I would get a row of of cells that go "=Sheet2!A1", "=Sheet2!A1", "=Sheet2!A1", etc.

However, I want the Sheet number to change with the cell number, so it would read "=Sheet2!A1", "=Sheet3!A1", "=Sheet4!A1", etc. Is there an easy way to do this? Or must I go in by hand and change the sheet number in each row individually?