"BJ Conner" wrote in message
ups.com...

riverman wrote:
In Excel, how can I make worksheet references relative?

For example, I want to insert the following in row 3, in cells A through
Z:

Sheet2!$B$5 Sheet3!$B$5 Sheet4!$B$5 Sheet5!$B$5 etc.

I know how to make the cell number absolute (with the $-signs), but I
cannot
get the 'fill' to change the sheets when I drag the function command
across
the different cells in row 3: it keeps the worksheet reference absolute,
referencing Sheet2. How can I make the worksheet reference relative, so
it
changes the sheet number for each successive cell?

--riverman

My smart person finally got back to me. I tried to send this to you
but it bounced. Here's the answer. It uses two extra rows which you
can hide.

start in row 1 as follows:

A1: =2
B1: =A1+1
C1: =B1+1
and so forth (by copying B1 to the right as far as you need)

Then, in row 2 generate the address text:

A2: ="Sheet"&A1&"!$B$5"
B2: ="Sheet"&B1&"!$B$5"
and so forth (by copying B2 to the right as far as you need)

Then, retrieve the values in row 3:

A3: =INDIRECT(A2)
B3: =INDIRECT(B2)
and so forth (by copying B3 to the right as far as you need)

IT worked for me.


Hey, I got it to work! (I had forgotten to type the exclamation point in the
second row). I see that the actual cell that I need to have in my program is
the INDIRECT command, and the other two get it to generate the command.

Excellent; thanks!

--riverman