Formulas Across Worksheets

[Guest]

I have formulas on multiple worksheets that reference to data on a primary
data worsheet (e.g. =DataSheet!$A$5). These multiple worksheets are forms
filled in with the data from the data worksheet. The problem I have is that
if the operator cuts and pastes some cells of data into another location, the
formulas on the form worksheets get realigned.
Example: A data entry in cell B4 is cut and pasted into cell B3. The result
on the form sheet that original referenced cell B4 via formula
=DataSheet!$B$4 now conatins =DataSheet$B$3 and the form sheet that original
contained the formula =DataSheet$B$3 now contains =DataSheet!#REF!

How can I prevent the formulas in the form workshees from changing the cross
worksheet reference formulas no matter what happens in the data worksheet?

 

[MartinW]

Hi Tom,

I'm a bit confused as to where you are cutting and pasting but
I think INDIRECT might help.
Change
=DataSheet!$A$5
To
=INDIRECT("DataSheet!$A$5")

HTH
Martin