Data Validation and Conditional formatting not carrying over when I copy sheeets

Question

I have a workbook that has several sheets with both validations and conditional formatting. When I copy these sheets it seems as if the validation/format is NOT following.

Is there something I need to do to carry that over. It's critical in data entry and validation.

Thanks,

TheOldPuterMan

Answer 1

Can you upload to OneDrive an example workbook with the same type of conditional formatting and data validation that you are using. I am assuming that your workbook might contain sensitive data so that is why I am asking for an example workbook rather than your main project.

Please fully test the example workbook to ensure it is behaving badly just as your main project.

I am not able to emulate your problem but with the many type of conditional formatting rules and data validation rules it might be that I am not using the same type so I would like to test with exactly what you have.

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file on OneDrive and select Share.
9. Select "Get a Link" from the popup menu.
10. Click in the field displaying the link and it should highlight and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)

Answer 2

Greatly appreciate your time and efforts... found my error and all is green now.

Great to hear. It is always a good feeling when one gets on top of a problem and resolves it.

Answer 3

OssieMac,

Greatly appreciate your time and efforts... found my error and all is green now.

I've worked with EXCEL for years but am just in the last few months stretching my horizons and using a lot of features for the first time.

Thank you again for your dedication and willingness to help others.

TheOldPuterMan

Answer 4

I use the copy, create new sheet and add to the end from the right clink menu on the tab of the sheet I want to copy, There are 5 sheets to the month and the first sheet contains ALL of the validation dates, values and lists that I reference for data entry.

The conditional formatting is done entirely based on cell values on the first sheet and serves to highlight conditions that I need to look at such as outstanding checks or out of balance payments/budget amounts.

That worked fine on the initial sheet and it SEEMS that the validations are still there on the copied sheets but are simply being ignored

TheOldPuterMan

Answer 5

I have not been able to emulate this problem. How are you copying the worksheets? Are you doing so with the following procedure.?

• Right click the worksheet tab name.
• Select Move or Copy.
• Check the "Create a Copy" box at bottom left of dialog.
• Select the destination in the DropDown at the Top. (Current workbook name, another workbook name or a new workbook.)

If the Data validation or Conditional formatting references different sheets to the one being copied then you might have a problem.