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

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

"ice" wrote in message
...
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

=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.

"ice" wrote:

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