Take a look at the OFFSET function. Use this in your formulas to refer to
user entered cells rather than the actual cell address.


"G Love" wrote in message
ups.com...
Hi there,

I've spent a long time creating a spreadsheet with numerous conditional
formats and complex formulae, I have locked the cells that must never
be changed and some of these react to data input by my colleagues into
other related cells. However, when they put a value in the wrong cell
they are in the understandable habit of copying and pasting the info to
the place it should be and this has the nasty result of screwing up the
cell references and thus returns errors in the locked cells.

What i need to do is prevent them from pasting over the cell formats
while still being able to copy and paste info (i.e. only allow paste
specialvalues).

I can't use a macro for restricting ctrl+v to paste special as most of
them don't use shortcuts they use mouse-clicks and visual buttons to do
their cutting copying and pasting.

Ideally what i would want is a sheet where all cells are locked but
have individual characteristics that allow different actions. e.g.;

A1 Totally locked, can't be selected, edited or modified
B1 Locked, can be selected and text entered but not formatted
C1 Locked, can be selected but will only accept input from
drop-down menu

Please tell me this is possible as it is frustrating to see so much
hard work ruined by a few inept clicks of a mouse, and if at all
possible I'd rather avoid macros.

Thanks

Mike