Excel Reference Question

[j_brubaker]

I am just going to explain what I am trying to do to see if any of you guys can help me.

Basically a simplified version of what I am trying to do is this:

I want to refer to cell A1 in sheet1 from A1 in sheet2. I have a macro set up to insert a row above row 1 every day in sheet1; however, instead of having the equation in A1 sheet2 switch from ='sheet1'A1 to ='sheet1'A2 when the row is inserted, I am looking to keep the ='sheet1'A1 equation the same even though the content of A1 sheet1 changed.

Does that make sense? And how can this be done?

Thanks
Josh

 

[PCL]

What is the formula in sheet2 making reference to cell A1 in sheet1 ???

 

[Rick Rothstein]

It will make the formula Volatile, but you could use the INDIRECT function...

=INDIRECT("Sheet1!A1")

 

[j_brubaker]

What is the formula in sheet2 making reference to cell A1 in sheet1 ???

I have the equation: =Sheet1!A1 in cell A1 in sheet2.

 

[PCL]

I have the equation: =Sheet1!A1

OK so you got the answer from Rick

 

[j_brubaker]

OK so you got the answer from Rick[/COLOR]

It worked!

However, now I am running into the problem of dragging down the page with the =indirect("Sheet1!A1") equation. I would like the next cell down to contain the equation =indirect("Sheet1!A2") and so forth. How can this be achieved?

 

[j_brubaker]

It worked!

However, now I am running into the problem of dragging down the page with the =indirect("Sheet1!A1") equation. I would like the next cell down to contain the equation =indirect("Sheet1!A2") and so forth. How can this be achieved?

So, I got it with this equation: =INDIRECT("'Sheet1'!A"&ROW(A1))