0

My case is little different I am working on an excel sheet so far It was a success but now I am totally clue less, what I am trying to do is I have two sheets sheet1 sheet2

In sheet1 I am calling data from sheet2 suppose

in sheet1 a5 I am calling data from sheet2 c3 by this formula 

=sheet2!c3

What I want to do is when I put a blank new row or column in sheet2 it automatically change the formula accordingly And I don't want it to do so, like it change it from

=sheet2!c3 into =sheet2!c4

I also tried putting $ 

=sheet2!$c$3

But it seems it only works on the same sheet not if you are calling data from another sheet, is there any way I can make this formula constant not change but remaining =Sheet2!c3 ??

Improve this question
2
  • The reason Excel behaves this way is for the day you actually want to add a blank row to your Excel sheet or re-position the displaying of your data. Why don't you insert the blank row underneath your formula? Things usually grow down and right.
    – CodeCamper
    Commented Dec 17, 2014 at 15:06
  • I agree, without understanding the use case - this is a very odd thing to want to do. There's a good reason why Excel does this if you think about it.
    – smackenzie
    Commented Dec 17, 2014 at 15:11

2 Answers 2

Reset to default
1

That's how Excel works when you insert rows I am afraid. You could try building the formula as a string value:

=INDIRECT("sheet2!C3",TRUE)

Improve this answer
3
  • Disclaimer for large workbooks: Volatile Function, fasten your seat belt and prepare for impact.
    – CodeCamper
    Commented Dec 17, 2014 at 15:03
  • It does, but I wonder how he would do it if he had 50k-100k rows from all that appending.
    – CodeCamper
    Commented Dec 17, 2014 at 15:26
  • that would be a separate question then...!
    – smackenzie
    Commented Dec 17, 2014 at 15:56
0

I just used the index function

=index(sheet2!c3,c,3)

and it works !

The above method is also 100%percent working and accurate.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.