Can I data merge specific cells into Word from Excel?

[sir42]

Is there a way to set up a data merge in Word that points to specific cells in Excel (ie. J32)?

Every day for work I have to do fairly complex spreadsheets in Excel and then e-mail an overview of what I put in my spreadsheets. It’s sort of like doing the work twice. First I enter all of the data into the spreadsheet, and then I enter it again in the body of the e-mail.

Is there way to set up a data merge in Word that can point to specific cells in my Excel spreadsheet? For instance, I’d like to have a generic form document that looks something like:

Wednesday’s gross sales: (J32)
Percentage of toys sold: (G40)

And then do a merge and replace J32 and G40 with the figures from those cells.

Perhaps I can set up an AppleScript to do this?

Any help would be greatly appreciated.

Thanks!

 

[iSaint]

I don't think there's a method to what your are asking, however....

Any reporting I've ever done I have used Excel for the final reporting document. Simply create a nice looking template in another sheet (within the same workbook) and create a link that will update when you update the working spreadsheet.

e.g. Sales: =(Sheet2!G30)
From the cell you want the data to be in, press =, then click on whatever cell has the data. It can be on the same worksheet, workbook, or in another workbook altogether.

Linking is a great feature. Plus you can format the viewable sheet almost like you can a Word document.

Then again, you could copy the cells of the viewable sheet (the one that looks nice) into the body of the email.

P.M. me and I'll send you an example if you wish.

 

[sir42]

That works brilliantly. Thanks for the tip! I also just discovered how to put both text and a formula into the same cell. This way I can have one cell that reads “Monday’s gross sales were X.”

The only catch is that the formatting doesn’t quite transfer into a Mail e-mail.

For instance, my Excel sheet looks something like:

Monday’s gross sales were X.
Tuesday’s gross sales were Y.

Percentage over previous week is XY.
Percentage over previous year is YX.

But when I copy and paste it into an e-mail it ends up looking like:

Monday’s gross sales were X. Tuesday’s gross sales were Y. Percentage over previous week is XY. Percentage over previous year is YX.

I’ve found that if I first copy it into Word and then into the Mail e-mail that my formatting is retained. But do you happen to know how to maintain the Excel formatting when copying into Mail so that I could do it directly?

Thanks again!

 

[iSaint]

I’ve found that if I first copy it into Word and then into the Mail e-mail that my formatting is retained. But do you happen to know how to maintain the Excel formatting when copying into Mail so that I could do it directly?

Glad that worked out for you. Yeah, the formatting won't look so great in mail. I always sent reports as attachments via e-mail. Now you can create a PDF easily (search PDF995 for free PDF creator) and email the one sheet as a clean report. I guess that's the same as a Word doc, but at least you know it can't be altered.

I would bet there's a way to put info in a mail document that retains formatting, but I don't know it offhand. Maybe your support people do as they are usually more knowledgable in the area of mail programs.

edit: in Mac you can print docs as PDF, or save as PDF docs so you wouldn't need the PDF creator

 

[rainman::|:|]

At my job, if we need a report-style page that links to data, we do the above method of using a sheet like a Word document, then linking to it's other sheets. Genenerally we just send the entire file, so they can see the underlying data if they want, or PDF the first page to protect that. But you can format an Excel sheet with text and graphics to the point where it looks like it was generated in Word... a lot of work once, but template it.

Sounds like the problem you are having is that you need to have the data in the email, rather than as attachment. Is this firm, or can you change the format of these outgoing emails? It's not unreasonable to attach things, I get weekly reports as PDFs and Excel workbooks all the time...

 

[sir42]

I’m afraid the e-mail part is firm. One of the people receiving the reports just wants to be able to receive the information on her Blackberry and not worry about opening up any attachments.

I’m actually having quite a bit of fun playing with these new formulas, so I don’t mind so much anymore after putting iSaint’s advice to the test.

Here’s a new question, however. I’m working on making one of the worksheets the report page. And I’ve succeeded in formatting it so that it looks mostly like a Word document.

It’s linked to another worksheet in which each column represents a day. For instance, Column B breaks down the sales made on Tuesday, Column C Wednesday, and so forth.

Is there a way to set the formulas in my report worksheet to magically switch to the next column over without changing all of the links by hand? So instead of linking to =’Sheet 1’!B3, it will link to =’Sheet 1’!C3. I’ll need to do a new report page everyday so this would also speed this along.

 

[iSaint]

Is there a way to set the formulas in my report worksheet to magically switch to the next column over without changing all of the links by hand? So instead of linking to =’Sheet 1’!B3, it will link to =’Sheet 1’!C3. I’ll need to do a new report page everyday so this would also speed this along.

I'm not quite sure what you're asking, but if I understand correctly...once you place one link in a cell, you can place the mouse at the bottom right of that cell to where it forms a 'plus' sign. Click and hold, and drag the cells across as far as you need. This copies the formula, and adds one cell link to the right...

e.g. C3, D3, E3

You can also drag down and the formulas copy

C3
C4
C5

If you double click the 'plus' sign, it fill copies down as far as there is something in the cell to the left of the empty cells. Sounds confusing but play around with it.

You can also make all sorts of links for days and weeks, then just hide certain cells on your report format.

Let me know how things are working out.