"Bernard Liengme" wrote...
If Sheet1 has the formula =Sheet2!A5 and then a new row in inserted above
row 5 on Sheet2 the formula automatically becomes =Sheet2!A6
The formula =INDIRECT("Sheet2!A5") is unchanged when Sheet2 is modified
best wishes
....

And if the OP needs many such formulas, the overuse of the volatile
INDIRECT function would really suck the performance out of the OP's
system. There's also the more immediate problem of entering or editing
the static references in many such formulas.

Better by far to use INDEX, e.g., if the value of Sheet2!A5 were
needed in cell X99 of the current worksheet and the value of Sheet2!B6
were needed in cell Y100, enter the following formula in X99, copy X99
and paste into Y100.

X99:
=INDEX(Sheet2!$1:$65536,ROWS($X$99:X99)+4,COLUMNS( $X$99:X99))

This particular formula for X99 will work the same as =Sheet2!A5 when
copying and pasting or dragging and filling.

INDIRECT and OFFSET functions are useful when used SPARINGLY. They're
nightmares when used promiscuously.