how to prevent formats from changing?

[flyers2thecup]

I receive spreadsheets from business lines with information regarding bonuses. I then copy/paste some of the information into a .csv file to upload into our system.

Some of the business lines bonuses are sales and they use formulas to generate the amount of the bonus. In doing this, it's not odd to see a number with 10+ digits past the decimal place. This causes an error if uploaded. Not to mention, if they use comma separator for thousands.

My spreadsheet template has the correct format, but if they don't consiously COPY/PASTE SPECIAL/VALUES, then it changes the formatting to that of what is getting pasted.

Is there a way to prevent that from working?? or a pop up message or something?

In fact, I wouldn't want ANY formatting to change on the ENTIRE sheet. Is that feasable?

Thanks in advance!

 

[just_jon]

Can you email me a sample .csv file, gholding no more than 10-20 records?

As well as a description of what you'd like the finished product to look like?

 

[flyers2thecup]

You can find the two files at:

http://www.trffl.com/junk/template01.xls
http://www.trffl.com/junk/template02.csv


Template01 is the payment request template that managers need to complete. As you can see, i plugged in awards with numbers well beyond two decimal places. that's one of the things i want to prevent. I also want the formats of the data to not change. (ie. font size, location, etc)

template02 is the csv file that i need to use to upload. i've already fixed the award amounts in there so they are only 2 decimal places.

thanks for the help. and sorry for the late response...i wasn't at a pc all weekend.

 

[just_jon]

So, in the csv file, columns A, C and H are the only ones of interest?

 

[just_jon]

So, in the csv file, columns A, C and H are the only ones of interest?

non-op bump

 

[flyers2thecup]

i swear i responded to this...

You are correct.

 

[just_jon]

That is correct. The .csv file always pulls the EXACT same information from the payment request.

(employee ID, earnings code, bonus, total, blank, blank, blank, managment unit)

the earnings code isn't on the payment request. there is a GL Account #, which is attached to a specific earnings code. if there is some kind of "if then" that can be written into it, fine. otherwise, i can manually put the earnings code in.

How many earnings code are there, and what are the eqiuv. GL Account numbers?

 

[flyers2thecup]

EC-GL
APS-266330
ASI-266340
APM-266350
AMI-266360
APG-266370
AGI-266380
AI5-266310
APY-266090
ATI-266300
ALT-266290

 

[just_jon]

Got an email address; have something for you to test.

 

[flyers2thecup]

I PM'd you my address last week. Did you get it?

 

[flyers2thecup]

haven't gotten anywhere with this....anymore ideas?