Referencing location in another sheet, I want the location to not change.

[Pife-Ster]

I am pulling data from a different sheet onto a overview worksheet. I tried as example =Sheet2!C3 The C3 is the data I want, but if I update that sheet(sheet 2), I insert a new row, put in the new data, and save it. The original formula changes to =Sheet2!C4....when the updated data is in C3. I also tried =Sheet2!$C$3 -it worked to pull the data, but the exact same thing happened when I inserted a new row. Should I just to this in a different workbook, and call it from that? Rather not, but I will if I have to.

I think calling it from a different workbook would work - since the overview page would never really know when data is moved around in the separate workbook. Guessing here.

Thanks for looking at this
Andrew

[Logit]

.
If you are using a macro to insert a new row, include in the macro code that moves the formula to the new C3

[bebo021999]

Try with INDIRECT to keep constant address of C3

=INDIRECT("Sheet2!C3")

[hrlngrv]

If there's be a lot of such formulas, INDIRECT would be problematic. The better alternative is =INDEX(Sheet2!1:1048576,3,3) which does depend on all cells in Sheet2, but Excel won't recalculate it if cells outside Sheet2 change.

[Pife-Ster]

Thanks for the quick responses, much appreciated.