formula that refers to B5 changes to B6

[laneman]

I guess I was a little unclear in my last post, sorry. In sheet one C7 I
have a formula that refers to B5 on sheet 2. The formula is:
=Sheet2!B5+1. All I want is to have sheet one, cell C7 return whatever
is in B5 plus one, even after the macro inserts a new row 5 on sheet 2.
So If B5 says 1001, and I then insert a new row from sheet one that
says 1002, I want C7 of sheet one to reflect the new number. The new
number is always one higher than the previous. The problem is that when
I (or the macro) insert a new row 5, the formula on sheet one then
refers to row 6. It changes to read =Sheet2!B6+1. I want C7 sheet 1 to
always refer to B5 sheet 2.

One post said try INDIRECT. The formula still changed to B6. One person
said try adding $B5$ around the B5. That did not work either.

 

[Vasant Nanavati]

=INDIRECT("Sheet2!B5")+1

should work.

 

[Andy B]

Hi

Try this:
=INDIRECT("Sheet2!"&ADDRESS(5,2))+1

 

[BrianB]

You could not have used INDIRECT correctly.
Try this :-
=INDIRECT("Sheet2!B5")+

 

[laneman]

Thanks, all. It works great