#REF! errors

[Guest]

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?

 

[Guest]

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.

 

[Gord Dibben]

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