Cell Referencing

[Guest]

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?

 

[JE McGimpsey]

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.

 

[Guest]

try:


in C3:

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

copy across