How to Reference a Cell

[Joe Schmo]

How do you reference a cell on a different worksheet. My problem happens
when the source cell get moved the reference follows it. I'm attempting to
have my formula always reference a certain cell regardless if the contents
of that cell have been moved. In other words my formula has to equal cell
C1 always ... even if the contents in C1 have moved.



Thanks,

 

[Jim Thomlinson]

=indirect("Sheet1!C1")

The above formula will always reference C1 on sheet 1 no matter what. The
only thing to note is that it is volatile and so it recaclulates every time
anything calculates anywhere in the application. To tha tend don't create
thousands of these types of formulas or you spreadsheet may slow down
noticeably...

 

[Joe Schmo]

Thanks, works great, however, is there anyway I can copy this formula down
multiple rows where the reference adds one. Becuase it reference a specific
cell I cannot fill down a series. For Example:

=indirect("Sheet1!C1")
=indirect("Sheet1!C2")
=indirect("Sheet1!C3")
=indirect("Sheet1!C4")

thanks,

 

[Gord Dibben]

=INDIRECT("Sheet1!C" & ROW(1:1))


Gord Dibben MS Excel MVP