Stopping automatic change of cell reference

[alion]

I have list of variables in sheet1 and their values are collected from sheet2. for example, for variable 1 the value is =sheet2!A2. So problem is that sheet2 gets updated with latest data using VBA so that existing data moves down one cell and new data is added to A2 then cell reference in sheet1 becomes =sheet2!A3. How can I stop this? I want the reference to stay at A2 and do not change to A3 automatically. I tried putting $ sign but it does not work. Anyone?

 

[AlphaFrog]

Try something like...
=INDEX(Sheet2!A:A, 2)

The 2 is the fixed row that you want to reference. In this example; Sheet2!A2

 

[alion]

Try something like...
=INDEX(Sheet2!A:A, 2)

The 2 is the fixed row that you want to reference. In this example; Sheet2!A2

thanks for the reply man. I tried this, but it is not working. When I add one row above the referenced cell, the formula changes to Index(Sheet2!A3). I want it to stay at A2.

 

[RoryA]

I suggest you recheck the formula that Alphafrog suggested.

 

[alion]

I suggest you recheck the formula that Alphafrog suggested.

I misunderstood.... Works now! thanks alot both of you...