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.