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?