formulas and text formatting

Delias9

New Member
Joined
May 24, 2010
Messages
2
In Win XP Pro I have set up an excel 2007 workbook to link to a Word 2007 document to produce printable Latin/English flashcards (2 columns by 4 rows per sheet i.e. 8 flashcards). Each side of the sheet is printed in a separate mail merge.
On sheet 1 of the workbook I have the raw data including columns for card no, Latin word and English translation.
On sheet 2 I use the following formula to extract the Latin words from sheet 1
='Unit 1_Vocabulary'!F2
On sheet 3 I use the following formula to extract the English words from sheet 1 and change the order so that the correct English translation is printed on the back of each Latin word.
=IF(MOD('Unit 1_Vocabulary'!$A3,2)=0,'Unit 1_Vocabulary'!G2,'Unit 1_Vocabulary'!G4)
This formula calculates wether the card no. is odd or even and then extracts the correct English translation.
I have copied these formulas down the relevant columns and they seem to work correctly but with 1 slight problem. When the text is extracted it loses any formatting applied in sheet 1 i.e. text colour, bold, italics etc. Is there a way to retain formatting or is there a better way to set up the workbook to do this task, I don’t know a lot about vba:confused:?
 
Last edited by a moderator:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Welcome to the Board!

When the text is extracted it loses any formatting applied in sheet 1 i.e. text colour, bold, italics etc. Is there a way to retain formatting or is there a better way to set up the workbook to do this task, I don’t know a lot about vba
I could be wrong, but I think that Excel can only send over data in a Mail Merge to Word, not formatting features like Color, Boldness, Italics, etc, similarly to if you export an Excel file to a text file, you only export data and not formatting features.

If there is some sort of logic you use to determine when certain values have these formatting features, maybe you can apply these formatting features on the Word side?

Maybe one of these links will be helpful:
http://www.gmayor.com/formatting_word_fields.htm
http://www.lifehacker.com.au/2008/1..._for_better-looking_tables_in_microsoft_word/
 
Upvote 0
Thanks. I think I will have to add formatting manually before I print.

Another issue I am having is trying to merge some data to the top left corner of the card, some to the top right corner and the rest of the data to be centred.
The centred data can vary from a single line to up to 4 lines, I therefore, don't think adding spaces and returns will work.

Any suggestions?
 
Upvote 0
Hi Delias9,

A Word mailmerge won't transfer character formatting, such as colour, bold, italics, etc, though data formats can be transferred if you use the DDE connection method.

As for the card data layout, that's just a matter of using the appropriate formatting tools in Word (eg left/right/centre-aligned paragraphs with left/right/centre-aligned tabs where appropriate).
 
Upvote 0

Forum statistics

Threads
1,225,819
Messages
6,187,202
Members
453,411
Latest member
healthcares

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