INDIRECT function to reference values in another worksheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rich - 01 Feb 2008 12:26 GMT

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.

Reply to this Message

Pete_UK - 01 Feb 2008 12:45 GMT

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

> 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
[quoted text clipped - 17 lines]
>
> Many thanks.

Reply to this Message