INDIRECT function to reference values in another worksheet

[Rich]

I have multiple worksheets each representing a year. In a summary
sheet I want to return values from each year's worksheet. I'm using
=INDIRECT("'"&A$1&"'!$B5")
in which A$1 returns the year at the top of the summary worksheet
which corresponds to the name of a single year worksheet (e.g. A1
contains "1975", which is also the name of a worksheet). Although this
works and returns the contents of cell B5 in worksheet 1975, my
problem is that when I copy the formula down a column the "$B5"
component doesn't update. So as I move down a column I want the
formula to read:
=INDIRECT("'"&A$1&"'!$B5")
=INDIRECT("'"&A$1&"'!$B6")
=INDIRECT("'"&A$1&"'!$B7") etc, but it remains =INDIRECT("'"&A$1&"'!
$B5").

(The A$1 component updates fine as it moves across rows, e.g. B$1, C
$1, D$1 etc.)

Any ideas?

Many thanks.

 

[Pete_UK]

It doesn't change because it is inside quotes, and so gets treated as
if it is text rather than a cell reference. Try something like this:

=INDIRECT("'"&A$1&"'!$B"&ROW(A5))

ROW(A5) will return 5 the first time you use it, but will increment to
ROW(A6), ROW(A7) etc as it is copied down, thus returning 6, 7 etc.

Hope this helps.

Pete