how to prevent formats from changing?

F

flyers2thecup

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

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Have you tried to import the file via Data | get external data, and have all fields formatted as Text? That should preserve formatting.
 
Upvote 0
This idea is that I don't want to have to do anything but the upload of the file. As of now, i have to check the formatting on EVERY payment request, and with the volume, it's getting to be a pain.



EDIT:

Sorry, I jumped the gun. misread your post. I've never used DATA - IMPORT DATA... can you explain how that works?
 
Upvote 0
What would be the destination sheet? The payment request (from business line) or the .csv file i'm pasting information into for upload?

The problem with payment request is that there are formulas that ARE needed. text would screw that up.
 
Upvote 0
Is the .csv in the exact same format all the time -- same # fields/record, same field sequence, same data layout, etc?

Does the data always go to the same place on your XL sheet?
 
Upvote 0
well, the template will change depending on how many people are getting bonuses out of a particular plan. so, rows get inserted for each employee.

so therefore, the .csv file will change as well.

did i answer that correctly?
 
Upvote 0
What I was wondering was, if it would be possible to write a macro to handle the import.

So, would we expect enough dta in the .csv to fill in x cells for each used row in the XL sheet on each import?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
How can I create a list from a single layer of a directory?
Replies
6
Views
320
PeteWright
PeteWright
S
  • Question
Conditional Formatting - How to Properly Copy + Paste without Duplicating Rules
Replies
1
Views
1K
6StringJazzer
6StringJazzer
E
  • Question
Best way to create a macro for creating a formatted text file.
Replies
1
Views
410
James006
James006
M
  • Question
Error 91 when pasting into outlook from excel using vba
Replies
3
Views
519
Micron
M
T
  • Question
How to automate/streamline reporting with Excel?
Replies
0
Views
339
TheAssetMgr
T

Forum statistics

Threads
1,215,329
Messages
6,124,301
Members
449,149
Latest member
mwdbActuary

Share this page

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