Absolute cell reference and REF error

[dswiders]

I have a large workbook with several difference sheets. The user can
make changes to the first sheet and information that is also on the
following sheets changes with it because I have absolute cell
references to the first sheet. However, when the user drags and moves
infromation from one cell to another, say from G13 to G15, then the
following sheets that are linked to those cells gives a reference
error because the formula has changed. I thought by doing the
absolute cell reference in the formula, =Sheet1!$G$13, that if the
user moved information to different cells on Sheet1 that formula would
not change so that the appropriate cells on the following sheets would
update as Sheet 1 was updated. Does anybody know of anyother method
that I can do so that I dont get a reference error?

 

[Peo Sjoblom]

Here are 2 options,

=INDIRECT("'Sheet1'!$G$13")

pros: easier to understand
cons: volatile which means that it will cause a calculation and you'll be
prompted to save workbook even if you didn't change or update anything,
these can seriously slow down a large workbook

=INDEX(Sheet1!1:65536,13,7)

pros: non volatile
cons: harder to understand