keeping conditional formatting after paste

I am making a large spreadsheet that others will use and they will have to 
paste large amounts of data onto the spreadsheet.  The issue is that when 
they paste their data the conditional format that I set up for them is 
removed.  Is there a way to lock the format in place and let them paste 
still?  I would be happy with protecting the sheet and allowing paste to 
still be an option.  
0
Utf
2/2/2010 2:51:57 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1466 Views

Similar Articles

[PageSpeed] 55

Instead of simple 'paste' try using 'paste special' > 'values'.
This will then only paste the copied value into the new cell's format.
Hope this helps

"ericahughes" wrote:

> I am making a large spreadsheet that others will use and they will have to 
> paste large amounts of data onto the spreadsheet.  The issue is that when 
> they paste their data the conditional format that I set up for them is 
> removed.  Is there a way to lock the format in place and let them paste 
> still?  I would be happy with protecting the sheet and allowing paste to 
> still be an option.  
0
Utf
2/2/2010 7:35:01 AM
Teach them to use the "Paste Options Button" and "Match Destination
Formatting"

From where is the source data that gets copied?

We can probably build some code that keeps the destination formatting.


Gord Dibben  MS Excel MVP



On Mon, 1 Feb 2010 18:52:01 -0800, ericahughes
<ericahughes@discussions.microsoft.com> wrote:

>I am making a large spreadsheet that others will use and they will have to 
>paste large amounts of data onto the spreadsheet.  The issue is that when 
>they paste their data the conditional format that I set up for them is 
>removed.  Is there a way to lock the format in place and let them paste 
>still?  I would be happy with protecting the sheet and allowing paste to 
>still be an option.  

0
Gord
2/2/2010 7:04:13 PM
Reply:

Similar Artilces:

Custom formatting #7
How can I do the following custom formatting – I need trailing dots before the number ……..1,234.00 Thanks for any help Right Click > Format Cells > Select Custom Enter "........ "0.00 "John Knoke" <JohnKnoke@discussions.microsoft.com> wrote in message news:586761E6-32A9-4911-BF50-3790614145B2@microsoft.com... > > How can I do the following custom formatting - > I need trailing dots before the number > ....1,234.00 > > Thanks for any help > > John, Use a custom number format of *.#,###.00 Note the period between the * and the f...

Creating conditional clause in a module
I am in the process of writing module using vba in ACCESS 2003 which imports forecasted financial data from a particular excel file based on three inputs. I am trying to include an "error check" that will end the process if the three inputs do not match specific data in the excel file. I'm not sure how to go about doing this. Do you have any ideas what the best way to do this would be? Below is the code I have for the import part so far if that is helpful. Sub Test1() Dim ImportDir As String Dim Forecast As String Dim Version As String Dim Country As Stri...

Convert time stored as decimalised number to time format
Hello How can I convert 3.5 hours to 3:30:00? -- Emma Emma A1/24 and formate as time Mike Rogers "Emma" wrote: > Hello > How can I convert 3.5 hours to 3:30:00? > -- > Emma ...

How to paste a cells row from Excel to a PowerPoint 2003 Table row?
Hello! How to paste a row of cells from Excel to a PowerPoint 2003 Table row? PowerPoint 2003 pastes all the row cells values in every cell in the row in the PowerPoint table if I select the row in the PP table before the pasting. PP pastes the row as an overlapping column if I place cursor in the first cell of the target row before pasting. Best regards, Dima +7 9163876746 +7 9035093892 ...

Keeping the format but update the data
I have a report I run everyday. I want to update the data, but keep the column sizes and formatting. Any suggestions? Anybody suggest a good ebook to become more formuliar with excel? JoeM JoeM, this could be as simple as recording a macro during the formatting of the sheet after importing the data once. Then each day, after importing the new data, you simply run that macro. As for online eBook's for Excel, I'm not actually aware of any although I suspect a Google search would turn up several. It's hard to beat the "For Dummies..." series of hardcopy...

I can copy but won't paste
I was able to copy and paste then not sure why but it would only paste the same txt over and over, not allowing new txt copied to be paste. Now I cant paste at all. It will copy but when I click paste it does nothing, help! This could be a poorly designed add-in interfering with copy and paste operations. Did you install something recently? -- Stefan Blom Microsoft Word MVP "Sarlou" <Sarlou@discussions.microsoft.com> wrote in message news:0E5E33F7-2387-4DBC-809E-AA97F930CEF5@microsoft.com... >I was able to copy and paste then not sure why but it would o...

NEED HELP-wrong formatting saved ??
After using word 2007 and saving a word file, it changed the formatting from the previous file (i think it was .wps microsoft works word processor) and now its all messed up.....I think I saved it in some other wrong encoding standard......now, whenever I open this file now half of it is in some weird looking unreadable characters....like this with wha捬屨捦ㅳ尠晡‰汜牴档晜獣‰歜牥楮杮尰扤档慜㍦㔱㔰楜獮獲摩㌱ㄳ〶‷਍灜牡素筽⩜灜獮捥癬ㅬ灜畮牣屭湰瑳牡ㅴ灜楮摮湥㝴〲灜桮湡⁧屻湰硴慴ges! That is what seems to happen once the system breaks its laws from the inside. ......couldnt find online solutions...does anyone have any suggestions or s...

Conditional Formating: linking to display another cell
Hello, I am trying to make a traffic light with symbols and I've read to 'use a separate cell for the dropdown choices, with their resulting value linked into the formatted cell through an IF function, using the character that you want to display.' So: =IF someone enters '1' in B8, THEN display contents of $C$4 (will it display font and attributes?) =IF someone enters '2' in B8, THEN display contents of $C$5 =IF someone enters '3' in B8, THEN display contents of $C$6 But I've been reading everywhere and CF is very new to me and I need ...

simple? pasting simple formula, error on copy
I have some store buying spreadsheets I made up. Very very basic, one of the columns has a total at the bottom or end as you may call it for total $ amount bought. that final number is in c33. c1, c2, c3 etc is a amount from each vendor. I want to have a column telling me the percentage being spent on each, simple enough so I make a column with the following formula =c33/c5. I get a number, change it to percentage and BAM I have exactly what I want. I pasted that formula and pasted it into all the other rows of that certain column, but I keep getting the #div/0 error, cause the new formulas...

Newbie Needs Help With Formatting E-Mails
Newbie Needs Help With Formatting E-Mails Hi all. I'm new to this site and would appreciate some formatting help. I created a new Word doc and copied and pasted some graphics and some simple text boxes in to it. The graphics and text boxes were created in Powerpoint by someone else. Looks fine on my screen. Before sending it out via e-mail to an Outlook e-mail group I have, I first sent myself a test message to see what it would look like on the receiving end. It looks a mess! The text boxes are all over the place, as is the text that is not in a box. Also, everything slide over to ...

2003 format in 2007?
I'm used to Outlook 2003, but I just bought a new computer and installed Outlook 2007. I view a week at a time, and I prefer the calendar format in 2003 -- it shows the whole week in two columns and I didn't have to scroll. Is there a way I can use the same format in 2007? No, unfortunately that view had to be removed to enabled calendar overlay. Try a two week view: http://slipstick.me/2wk -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dai...

Outlook keeps making itself Default
Hi, Everytime I start Outlook it takes over as default for email and addresses even though I have that option unchecked in outlook, and in IE I set OE Express as default, yet each time I launch outlook it takes over as Default, any ideas TIA Wayne ...

formatting #2
Is the strikethrough formatting option no longer available in Publisher? Or if you need to use a strikethrough do you have to draw a line through the text? The line does not always stay in the same spot when you group and then have to paste on another page. Waggie wrote: > Is the strikethrough formatting option no longer available in > Publisher? Or if you need to use a strikethrough do you have to draw > a line through the text? The line does not always stay in the same > spot when you group and then have to paste on another page. ========================== You have the an...

Trying to get a type of video file format to open and run on my we
I created a video using Windows Movie maker and saved as .wmp file. I saved it to my computer and then inserted on my web page under Front Page 2003. Nothing! I then converted the file to avi format - still nothing! I then tried mpeg4 still nothing. The manual is next to useless on this subject. Anyone tell me what I need to do so that when a user opens up the page the video plays automatically. -- Simon Holloway Save as a .wmv and insert a suitable player into the page. For IE only, Insert->Web Component->Advanced Controls->ActiveX Control Click Next Choose Window...

Text formatted email
I am trying to email a long hyperlink path to some one that can only receive text formatted emails. When they receive the email it, the hyperlink gets converted in to 2 lines, top line is a hyperlink (path incomplete) the second line is "text" with the balance of the hyperlink path. I placed < > around the hyperlink and it still splits in to 2 lines. Is there any way to keep the full link path intact? Thanks! jvs Tiny URL to the rescue! :-) http://tinyurl.com/ -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Cr...

Import data not in correct format
Import data reads: Need to do statistical anaysis on hundreds of numbers and Cell value # of Cells don't want to enter the numbers by hand. 3 1 4 2 Would like: Cell Value 3 4 4 ...

Four Conditional Formats?
Dear all, I am attempting to set up a template for someone else to use on another machine, and wish conditional format a cell such that it has four potential formats. The first is based on an adjacent cell value, and if this is met, then it is colour 1. The remaining three, are all AND statements involving the first cell and another cell value being less than date A, less than date B or less than date C. Is this possible to do? I have limited VB knowledge, and am wary of using add-ins as the people I am sending this to to use have limited excel knowledge. Regards, Steve -- stevepain -...

Conditional Format
Hi, I want to assign a number to a certain conditional format. When the condition is true it should put a 1 in the cell and when the condition is false a zero or nothing should be assigned. Anyone? I seem to make the wrong code all the time. Thanks, Johanna Use the same test that the CF uses, and return 1 for TRUE and "" for FALSE. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jootje" <Jootje@discussions.microsoft.com> wrote in message news:CB0B3201-2DD9-466A-94FF-108691C77FAF@microsoft.com... > Hi, > >...

my formats get pushed out when I insert columns
Hi folks, I am having trouble when inserting columns in a large data field. Whenever I insert a column at some point in the data field, th formulas within the data field adjust, or 'stretch', to accomodate th added column, however my conditional formatting gets pushed along t the right and doesn't adapt itself in the same way the formulas do. This means whenever I insert a column, the formulas remain correct bu I have to reset the conditional formatting for the entire data field. I would be extremely grateful for any advice on this situation. Best regards, melvi -- Message pos...

paste linked cells show "0"
I have a number of cells in a range (lets say A14:J52). Some of these cells are "paste linked" from cells on other sheets. Unfortunately, when the original cell is empty, the value of the "paste linked" cell shows as "0". (not just blank like the original) Is there a way to keep the "paste linked" cell showing "0"? If not, how would I go about using VBA to systematically go through the above described range and delete those zeros? At the point I ran this code, any "0" could be deleted from the range safely whether it ...

Dynamics CRM 4.0
Hi, I've modified the format in the system settings screen to be "English (United Kingdom)", however all dates are still appearing in US format. Even when you expand the calendar control it displays incorrectly. Numeric values are correct - showing the pound sign and correct decimal point. I've restarted IIS, made sure all SQL logins are British English and the default regional settings of the server are set to British English. Even rebooted the server but this still hasn't corrected the problem. Can anybody tell me how to resolve this please Thanks I was looking ...

Custom Formatting numbers question
I have a spreadsheet with lot of millions numbers floating around. When I make graphs out of it, there's too much zeros. I don't want to divide all the numbers per 1 million. Is there a way to custom format those numbers with a mask that would take care of that problem for me ? i.e. 7 456 890.98$ would be represented with 7,457 M$ tia Double-click the numbers on your chart. Hit the Scale tab. From the Display units drop-down, choose Millions. ******************* ~Anne Troy www.OfficeArticles.com "Junkyard Engineer" <jevandenbroucke@hotmail.com> wrote in messag...

Changing cell format
I have a worksheet that is populated with data exported from Access. The data in the Access table was serial numbers with an occasional (rare) alpha character. The column was formatted "Text" in Access. When I exported it to Excel, every value in this field (around 3000 entries), has an apostrophe (') inserted at the frontof the cell. Find and Replace does not see the ' How can I remove this so that I can use Fill>Series to populate the gaps in my data (there are many gaps)? Try copying the ' Access character and pasting it into the Replace menu. It could be...

keep lover level bullet list with previous bullet
Word 2007. I have a Heading then some paragraphs. Then a bullet list. How do I prevent a page from breaking in the middle of a lower level bullet list? In other words, how do I keep lower level bullets with it's upper level? For example: Many paragraphs o upper level bullet aaa - one aaa - two aaa - three aaa o upper level bbb - one bbb - two bbb - three bbb Right now I run out of room on the page and -two bbb goes to the next page. I can highlight the "bbb"s and under Paragraph/line and Page Breaks check Keep Lines Together. I may nee...

Trouble with copy and past of formulas in Excel
I got a sheet from one of my collegaes that was causing problems, it sometimes looses formulas (It looks like a problem with connections through our network) and you get true untrue name errors, after refreshing the cell the problem is gone. Now I'm having trouble with copying formulas, after trying some copy and paste it crashes with dr. Watson. It seems that there is some timeout problem. More people have trouble with this with the somewhat larger sheets. ...