=INDIRECT, Sheet reference with Incremental cell reference

Hi Everyone

I've used the INDIRECT function several times, but never fully understood it.

What I'm trying to do now is create 1 sheet with all the values from A2:A499 on 29 different sheets, Named Sheet1 - Sheet29.

On my new sheet i'm using to compile the date, the headings in Row 1 are Sheet1 - Sheet29.

Then in A2 I have =INDIRECT("'"&A$1&"'!$A2").

This means, as I drag that equation around. the indirect reference will automatically adjust it's column reference but will always look at the value in row 1. and no matter what sheet is is looking at, it will always pull the value from column A, but adjust the rows accordingly

The problem I have is that the cell reference value "$A2" is not changing. as I copy the equation to different cells. It always stays as "$A2". Even if I manially adjust to "$A3" & "$A4", Then select those three cells and drag the selection down to copy, all this does is repeat that same sequence of 3, it doesn't continue the count.

Any ideas what I can do?

Thanks Miroslav, that works perfectly (with a slight adjustment adding another $ to affix the cell ref column)

=INDIRECT("'"&A$1&"'!A"&ROW($A3)-1)

I'll be honest though, I don't understand "why" this works, but a big part of that is me not understanding Indirect fully, or Offset at all is offset the bit,

Why did you need the "(A2)-1", Whay couldnt you just put "(A1)"? As I've tested and that works too.