Pasting of Formulas and Formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2317660%22%20slang%3D%22en-US%22%3EPasting%20of%20Formulas%20and%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2317660%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%26nbsp%3B%20I%20have%20a%20shared%20spreadsheet%20with%20table%20in%20it.%26nbsp%3B%20It%20includes%20data%20validation%20and%20one%20formula.%26nbsp%3B%20Other%20individuals%20either%20directly%20enter%20information%20or%20copy%20and%20paste%20from%20other%20sources.%26nbsp%3B%20The%20direct%20entries%20are%20no%20problem%2C%20as%20the%20drop%20downs%20and%20data%20validation%20work%20well.%26nbsp%3B%20When%20information%20is%20pasted%20from%20an%20outside%20sheet%20though%2C%20it%20is%20very%20easy%20to%20paste%20the%20external%20formulas%20and%20formatting%2C%20which%20can%20%22mess%20up%22%20the%20table%20and%20cause%20the%20need%20to%20%22repair%22%20it%2C%20which%20takes%20time.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20requested%20they%20paste%20values%20only%2C%20but%20this%20scenario%20is%20still%20happening.%26nbsp%3B%20Is%20there%20a%20way%20to%20control%20pasting%20and%20only%20allow%20values%20to%20be%20pasted%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3EAaron%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2317660%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2322316%22%20slang%3D%22en-US%22%3ERe%3A%20Pasting%20of%20Formulas%20and%20Formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2322316%22%20slang%3D%22en-US%22%3EThis%20is%20one%20of%20those%20gotchas%20that%20really%20needs%20a%20fix%20by%20Microsoft.%20All%20I%20can%20suggest%20are%20workarounds.%20I%20wrote%20about%20this%20some%20years%20ago%3A%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fjkp-ads.com%2Farticles%2Fcatchpaste.asp%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fjkp-ads.com%2Farticles%2Fcatchpaste.asp%3C%2FA%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello.  I have a shared spreadsheet with table in it.  It includes data validation and one formula.  Other individuals either directly enter information or copy and paste from other sources.  The direct entries are no problem, as the drop downs and data validation work well.  When information is pasted from an outside sheet though, it is very easy to paste the external formulas and formatting, which can "mess up" the table and cause the need to "repair" it, which takes time.  

 

I've requested they paste values only, but this scenario is still happening.  Is there a way to control pasting and only allow values to be pasted?

 

Thanks, 

Aaron

1 Reply
This is one of those gotchas that really needs a fix by Microsoft. All I can suggest are workarounds. I wrote about this some years ago:
https://jkp-ads.com/articles/catchpaste.asp