I have data in sheet1 in cells A1:A5.

In cell C3 of Sheet2 I want to place this formula "=Sheet1!A1". Based on
that formula lookup, I want Cell D3 to look at Sheet1!A2, Cell E3 to look at
Sheet1!A3, and so on ...

If I changed the formula in cell C3 of Sheet 2 to "=Sheet1!B1" I would want
all the other cell references to automatically change to Sheet1!B2,
Sheet1!B3, etc.

Does anyone know how to do this?

One way:

Choose Insert/Name/Define:

Name in Workbook: BaseCol
Formula: =Sheet1!$A:$A

In Sheet2:

C3: =INDEX(BaseCol, Column()-2)


To change to B1,B2, change the definition to Sheet1!$B:$B using
Insert/Name/Define.


In article ,
Tim Caldwell wrote:

I have data in sheet1 in cells A1:A5.

In cell C3 of Sheet2 I want to place this formula "=Sheet1!A1". Based on
that formula lookup, I want Cell D3 to look at Sheet1!A2, Cell E3 to look at
Sheet1!A3, and so on ...

If I changed the formula in cell C3 of Sheet 2 to "=Sheet1!B1" I would want
all the other cell references to automatically change to Sheet1!B2,
Sheet1!B3, etc.

Does anyone know how to do this?

try:


in C3:

=INDIRECT("Sheet1!A" & COLUMN()-2)

copy across

"Tim Caldwell" wrote:

I have data in sheet1 in cells A1:A5.

In cell C3 of Sheet2 I want to place this formula "=Sheet1!A1". Based on
that formula lookup, I want Cell D3 to look at Sheet1!A2, Cell E3 to look at
Sheet1!A3, and so on ...

If I changed the formula in cell C3 of Sheet 2 to "=Sheet1!B1" I would want
all the other cell references to automatically change to Sheet1!B2,
Sheet1!B3, etc.

Does anyone know how to do this?

Switch between relative, absolute, and mixed references
1.. Select the cell that contains the formula.
2.. In the formula bar , select the reference you want to change.
3.. Press F4 to toggle through the combinations. The "Changes To" column
reflects how a reference type updates if a formula containing the reference
is copied two cells down and two cells to the right.


Formula being copied

Reference (Description) Changes to
$A$1 (absolute column and absolute row) $A$1
A$1 (relative column and absolute row) C$1
$A1 (absolute column and relative row) $A3
A1 (relative column and relative row) C3


--
Don Guillett
SalesAid Software

"Tim Caldwell" wrote in message
...
I have data in sheet1 in cells A1:A5.

In cell C3 of Sheet2 I want to place this formula "=Sheet1!A1". Based on
that formula lookup, I want Cell D3 to look at Sheet1!A2, Cell E3 to look
at
Sheet1!A3, and so on ...

If I changed the formula in cell C3 of Sheet 2 to "=Sheet1!B1" I would
want
all the other cell references to automatically change to Sheet1!B2,
Sheet1!B3, etc.

Does anyone know how to do this?