INDIRECT function to reference values in another worksheet
 

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.

INDIRECT function to reference values in another worksheet
 

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

On Feb 1, 12:26*pm, Rich wrote:
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.