changing parts of formula
 

Is there any way I can adapt how a formula copies across, apart from doing it
manually? I want part of the formula to change. For example Sheet1!$a2 when
copied across will show Sheet1!$a2 and so on. I want it to show Sheet2!$a3
and then Sheet3!$a2.

changing parts of formula
 

Enter this in A1 of a new sheet then drag across.

=INDIRECT("Sheet" & (COLUMN()) & "!A2")


Gord Dibben MS Excel MVP

On Sun, 31 Aug 2008 05:54:00 -0700, Chay
wrote:

Is there any way I can adapt how a formula copies across, apart from doing it
manually? I want part of the formula to change. For example Sheet1!$a2 when
copied across will show Sheet1!$a2 and so on. I want it to show Sheet2!$a3
and then Sheet3!$a2.

changing parts of formula
 

BIG thanks to Gord and Roger ! Will save me hours of manual entry !

"Roger Govier" wrote:

Hi Gord

I think the OP wanted to step up the row number as well each time, so I
suspect you meant to write
=INDIRECT("Sheet" & COLUMN() & "!A" & COLUMN()+1)

--
Regards
Roger Govier

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Enter this in A1 of a new sheet then drag across.

=INDIRECT("Sheet" & (COLUMN()) & "!A2")


Gord Dibben MS Excel MVP

On Sun, 31 Aug 2008 05:54:00 -0700, Chay
wrote:

Is there any way I can adapt how a formula copies across, apart from doing
it
manually? I want part of the formula to change. For example Sheet1!$a2
when
copied across will show Sheet1!$a2 and so on. I want it to show Sheet2!$a3
and then Sheet3!$a2.