SEARCH

The How-To Geek Forums Have Migrated to Discourse



Join the Discussion at discuss.howtogeek.com

How-To Geek Forums / Microsoft Office

Mail Merge issue with word 2003 and excel data.

(4 posts)
  • Started 3 years ago by BigGigs
  • Latest reply from raphoenix
  • Topic Viewed 1312 times

BigGigs
Posts: 2

I have created a very complex excel data base using excel 2003. The spreadsheet contains a large number of columns and uses formulea to create a description of a package of support which involves personal information of the service user and the value of their support package calculated out into hours of support and both the value of elements of the package and the value of the total cost.

This spreadsheet is then connected to a mail merge document I have created in word 2003. This document forms the basis of a contractual description of the package and all its different elements and their values. As such the document has a set format and uses mail merge fields to transfer specific data and costings relevant to each person's package. As may colleagues have to do about 100 of these contracts and they need to be updated, the information has to be transferred accurately.

My problem is that having set the spreadsheet up to automatically calculate through the value of a person's package, rounding this up or down to two decimal places. When the value that appears in the spreadsheet is transferred over to the word document - the formatting control on the number of decimal places is lost and the figure that had been to two decimal places on the spreadsheet, is displayed in the word document as a figure with nine decimal places.

Can someone please advise me as to how I can stop the mail merge document displaying the transferred data to nine decimal places, and keep it as it appears on the spreadsheet.

Any advice would be most welcome and will stop me from getting lynched by work colleagues, who don't think my "bright idea" is actually very helpful as they still have to edit the documents down from nine decimal places.

Cheers Giles

Posted 3 years ago #
Top
 
raphoenix
raphoenix
Posts: 14920

BigGigs,
---
Your idea is the Correct way to proceed. :) :)
---
There is a function(s) / formulas in Excel that will Truncate the rounded resulted to (X) number of places.
---
I would need to review all functions and perhaps re-do a formula BECAUSE it's been so long I can't remember now BUT is very possible. :) :)
---
Use to do it all the time many years ago.
---
Grab a Good Excel Book or Study the Function Help and become the Office Excel Guru. (LOL)
---
If you learn to do Macros, you can even fool the boss into thinking you are a real programmer and ask for a higher salary. (LOL) :) :)
TRUE
--
Best Regards,
Rick P.

Posted 3 years ago #
Top
 
BigGigs
Posts: 2

Thanks for you prompt attention and response Rick, I'll try the macros rouse, as I could do with a promotion. However, the issue is that the excel function that truncates (nice description) the final value to only two decimal places is lost when I do the mail merge over to a word document.

Could you recommend any good excel advice books.

Cheers Giles

Posted 3 years ago #
Top
 
raphoenix
raphoenix
Posts: 14920

May have to insert another row and / or column. ALSO see PRECISION in Help.
========================================

COPIED FROM MS EXCEL HELP SCREEN
==============================================

FIXED
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as TEXT. < My Emphasis >

Syntax

FIXED(number,decimals,no_commas)

Number is the number you want to round and convert to text.

Decimals is the number of digits to the right of the decimal point.

No_commas is a logical value that, if TRUE, prevents FIXED from including commas in the returned text.

Remarks

Numbers in Microsoft Excel can never have more than 15 significant digits, but decimals can be as large as 127.
If decimals is negative, number is rounded to the left of the decimal point.
If you omit decimals, it is assumed to be 2.
If no_commas is FALSE or omitted, then the returned text includes commas as usual.
The major difference between formatting a cell containing a number with the Cells command (Format menu) and formatting a number directly with the FIXED function is that FIXED converts its result to text. A number formatted with the Cells command is still a number.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
A
Data
1234.567
-1234.567
44.332
Formula Description (Result)
=FIXED(A2, 1) Rounds the first number 1 digit to the right of the decimal point (1,234.6)
=FIXED(A2, -1) Rounds the first number 1 digit to the left of the decimal point (1,230)
=FIXED(-1234.567, -1, TRUE) Rounds the second number 1 digit to the left of the decimal point, without commas (-1230)
=FIXED(44.332) Rounds the third number 2 digits to the left of the decimal point (44.33)

COPIED FROM MS EXCEL HELP SCREEN
----
Regards,
Rick P.

Posted 3 years ago #
Top
 



Topic Closed

This topic has been closed to new replies. Please create a new topic instead.


Create New Topic Return to Forum