Excel - Force Paste Special

[grnzbra]

I have a workbook that is a revision of prior workbooks. The cell formulas have changed. I find that if one does a CTRL + C and CTRL + V to copy info from an older version, the formulas are copied over and are now pointing to the wrong cells. Is there any way to set the properties of a workbook to force the user to use Paste Special?

 

[SkipVought]

HI,

So what paste special feature do you want the user to choose?

What I might suggest is that YOU, ONE TIME, COPY ALL the data on each sheet and paste special VALUES before turning it over to users.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[grnzbra]

We're not the ones doing the copying and pasting. The customer's user will be doing that.

 

[SkipVought]

I know that. What I'm asking is if the user is not pasting FORMULAS, then what do you expect them to be pasting? Anything BUT formulas?

If so YOU can the get rid of the formulas, so that ctrl+v will work as intended.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[grnzbra]

I don't know what they will be copying from. It may be from an old version which has formulas. The sample they gave us did have formulas in it.

 

[SkipVought]

You stated, "I have a workbook that is a revision of prior workbooks."

If you have such a workbook, by YOU using paste special VALUES, you can remove the formulas leaving the values along with all the original formatting, making it possible for your users to copy and paste to their hearts' content with no formulas in sight.

There is no setting that would make ctrl+v pop up paste special. Of course, your users could be trained on how to properly use copy/paste under various conditions. This is an elementary level feature on the spectrum of features.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[grnzbra]

All of the formulas from the old workbook have been either replaced, modified or deleted. We will be giving them a workbook containing the current formulas but no values. If they type in the values there is no problem. If they copy them from a text file there is no problem. But if they copy them from a file containing formulas and overwrite the existing formulas, there are problems.

Training would be nice, but I don't want to depend on them being conscientiousness about it.

 

[SkipVought]

You have a problem with your people. You can't fix stupid.

What you perhaps could do is make all the cell containing VALID formulas, LOCKED. then protect the sheet. Then nothing could be pasted into those cells.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

The first thing I would do is what Skip suggest, is NOT HAVING FORMULA IN YOUR SPREADSHEET (i.e., having only text and values)
If you must have formulae, make them cell specific (e.g., change any reference like B4 to $B$4) so they're an absolute reference and copy & pasting them won't change the resultant calculation.

 

[combo]

There is a scenario when regular copy&paste copies values:
1) in the source worksheet select cells with formulas to be copied, set hide formulas for protection option,
2) protect worksheet.
If you have both input cells and formulas, unlock cells that user can fill before protecting worksheet.

combo