I have several application workbooks that use a 2 sheet approach.

Sheet 1 is designed for end user input. It is pre-formatted and allows the
end-user to input only the pertinent data

Sheet 2 is very structured with very specific formulas. Its purpose is to
recalculate and rearrange the data for a specific inport into another program.

Everything works great until the end user uses cut and paste to move data
that was incorrectly entered on Sheet 1 which creates #REF! errors on Sheet 2
whereever the data was moved.

I have used both absolute and realative referenced formulas with the same
result.

Any suggestions?

It might be possible to adapt it to use INDIRECT to always point to the
referenced cell, eg in Sheet1's B2: =INDIRECT("'Sheet2'!C2") would always
return what's in Sheet2's C2, regardless. Perhaps you could post samples of
your formulas which are currently vulnerable to mutilation by the users.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bbotzler" wrote:
I have several application workbooks that use a 2 sheet approach.

Sheet 1 is designed for end user input. It is pre-formatted and allows the
end-user to input only the pertinent data

Sheet 2 is very structured with very specific formulas. Its purpose is to
recalculate and rearrange the data for a specific inport into another program.

Everything works great until the end user uses cut and paste to move data
that was incorrectly entered on Sheet 1 which creates #REF! errors on Sheet 2
whereever the data was moved.

I have used both absolute and realative referenced formulas with the same
result.

Any suggestions?

How did the user enter incorrect data if the formatting "allows the end-user to
input only the pertinent data"?

I would suggest not allowing the users to enter incorrect data by using Data
Validation in their input cells and sheet protection to not allow them to select
non-pertinent cells.

Then running a macro which would move only the correct data to the target sheet
at the range you specify in your macro.


Gord Dibben MS Excel MVP

On Mon, 26 Feb 2007 15:03:00 -0800, bbotzler
wrote:

I have several application workbooks that use a 2 sheet approach.

Sheet 1 is designed for end user input. It is pre-formatted and allows the
end-user to input only the pertinent data

Sheet 2 is very structured with very specific formulas. Its purpose is to
recalculate and rearrange the data for a specific inport into another program.

Everything works great until the end user uses cut and paste to move data
that was incorrectly entered on Sheet 1 which creates #REF! errors on Sheet 2
whereever the data was moved.

I have used both absolute and realative referenced formulas with the same
result.

Any suggestions?