need to prevent pasting of wrong format

flyers2thecup

Active Member
Joined
Jul 29, 2004
Messages
362
I have a payment request spreadsheet that goes out to different business lines. On this form, are people who are getting incentive awards. I have to take the awards and save as .csv files and load the awards into peoplesoft. Some of the awards are based on sales. Therefore, some of the business lines use their own formulas to derive the amount that goes onto my template. But I need the awards to stay two decimal places. They are cutting and pasting and I get numbers like 1244.4596825. Is there a way to make a message popup to say, "The amount you are trying to paste is more than two decimal places. Please correct and enter a valid number."


Also, is there a way to lock just a CELL? not the sheet. I need them to be able to enter info...but some people change my formulas and it makes me very angry!!! :devilish: :devilish:

Thanks in advance!!!
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
For the first part highlight the cells in question select data, validation, chose custom, and in the formula bar put this:
=IF(ISERROR(FIND(".",A1)),TRUE,LEN(TRIM(MID(A1,FIND(".",A1)+1,100)))<=2)

On the second part of your question what you do is highlight the ranges you want to be data entry fields. right click, select format cells, select the protection tab, clear the check mark for locking the cells. THEN Lock the spreadsheet. All the areas you did not lock will be protected while the unlcoked area can be used.
 
Upvote 0
thanks for the quick reply!!!

for the first part, will it work if people insert rows? my default template has 10 rows....but can need hundreds of rows sometimes that need to be inserted by the business line.

edit:

Also, I've named the range AwardTotal. Do I just replace the A1 with AwardTotal?

Edit #2:

I pasted the formula as is...results:

1: If i just type a number 5.55555, it converts to 5.55 in the cell, but the true number is still 5.5555. Can't have that.
2: If i paste in 5.5555 it just stays 5.5555.

Doesn't seem that formula is what i'm looking for. is there no way just have message pop up?



Thanks again!!!!
 
Upvote 0
Howdy. Pasting in data will over-write the validation. The only way around it is to use VBA. Is that an option for you?
 
Upvote 0
Protected cells...

I've come to terms with the fact i won't be able to keep the formats the same in my sheets.

i'm going the route of protecting the sheet....and unprotecting cells i want to allow. with the sheet locked, i see that i cannot add rows. is there a way to allow rows to be inserted on a protected sheet? vba?

or can i have a button that says "INSERT ROWS" and then asks "HOW MANY EMPLOYEES ARE ON THIS REQUEST?"

thanks.
 
Upvote 0
As far as keeping the formats themselves, on thing you can do is set up a reformat sheet macro, where you just run it and it formats the sheet the way you want to. I have set that up on on QC sheet I do that gets a lot of pasted in values. I just let them paste to their hearts content and then any time they want to "neaten it up" they can hit the button and it will make it look proper.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top