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!!!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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.
 

flyers2thecup

Active Member
Joined
Jul 29, 2004
Messages
362
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!!!!
 

shades

Well-known Member
Joined
Mar 20, 2002
Messages
1,550

ADVERTISEMENT

Howdy. Pasting in data will over-write the validation. The only way around it is to use VBA. Is that an option for you?
 

flyers2thecup

Active Member
Joined
Jul 29, 2004
Messages
362
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.
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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.
 

flyers2thecup

Active Member
Joined
Jul 29, 2004
Messages
362
hmm. sounds like a good idea...but not sure how it would work if the amount of rows is never the same.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,335
Messages
5,571,608
Members
412,407
Latest member
ElmerCC
Top