Indirect reference

[ice]

Hi,
I want to use the indirect() function.

A1 of the current sheet says sheet 2
So I do the =INDIRECT("'" & $A$1 & "'! & B2")
Which returns the contents of Sheet 2 B2, which is great...but what I
want to do is have B2 be relative,
When I do a copy paste into the following 20 rows, I want it to be
B3..B22. If I take it out of quotes it references the B2..b22
contents of the current sheet. in the quotes it stays B2 through out.
Basically I want
='Sheet 2'!B2
='Sheet 2'!B3
='Sheet 2'!B4
..
..
='Sheet 2'!B22

But the part I want to reference is the Sheet 2 part
Any idea, thanks

 

[David Biddulph]

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

 

[Sam Wilson]

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


You'll have to change ROW() to ROW()+x if you're putting the function x rows
after row 2 etc.