Transferring Excel Data to Word "form/fields"

**smasian** · 12-27-2012, 06:11 PM

Hi,

I am working on Open Enrollment for my company. I have received figures from all our insurance companies for the pay-roll deduction of all employees and inputted it into an Excel worksheet.

Now, I also have a form that was created in Microsoft Word that I need to fill out for every employee. This form has the employee's information as well as how much we would be deducting from their pay every paycheck which needs to be signed and turned in. The problem is, I have 300 employees and it would be a lot helpful if I could somehow extract the data from Excel to be placed into the Word Document.

Does anyone know how to do this?? Please help!!

**lilsnoop** · 12-27-2012, 07:48 PM

Can you post a sample excel workbook and word doc? I transpose data from excel into Word for some reports I do, but it would be difficult to explain or witness your circumstances without these documents.

**smasian** · 12-27-2012, 08:41 PM

YES!!! Thank you so much for your reply!

Attached are two documents. The Excel Sheet is the payroll deductions I will be putting into our Payroll System. The second is the Employee agreement where I have to input the 2013 amounts based on the Excel sheet. Deduction Agreement.doc Payroll Deductions.xls

**lilsnoop** · 12-27-2012, 09:48 PM

I've modified your excel sample with some formulas in column A & C with some comments added. I've also created another sheet titled "Filtered". On your "ABC" sheet once you filter the name of whomever.. it will transfer all of the data for that individual indicated over to the filtered sheet. This sheet can for all purposes be hidden if you prefer. The data on this titled filter sheet will transpose itself onto the open word document. The sample you provided didn't really match up to the columns titled in the excel document at least to my interpretation. So I only made it so the name would would automatically update in the open word document. To make the other the data transfer automatically you will need to do the following:
1. copy the appropriate cell in whatever column i.e. B2, C2, etc.. individually. So for instance copy cell B2 then
2. Go to your word document and find the actual place on your document that you would want that data to be put. Once you have your cursor set you will need to "Paste Special", click on "Paste link" and then select "unformatted text". You will need to repeat this process for each excel column/cell in row 2 into the appropriate area of your word document.

I would have done more, but again I was not certain where some of the data was suppose to go. Also you will be prompted with a message when you initially open your word document, indicating there are links.. you will want to click "yes", otherwise the communication between the excel document and the word document will not take place. I hope this is helpful!

**smasian** · 01-10-2013, 01:24 PM

Thank so so so so so much!

I really don't know how else to show my gratitude. You just saved me so much time!

I do have one question though, the links don't seem to be automatically updating. How do I fix this?

Thanks again!

**lilsnoop** · 01-10-2013, 02:11 PM

Sometimes you have to highlight the field and right click and you'll see "update link" to re-associate the embedded link. I have to do it occasionally myself, but most of the time it works flawlessly. The problems I would normally have is if I copy and replace my original excel document with a newer file with the same title, etc and I have to edit my links afterwards. When you open your word document though you should be prompted to update links too. Glad I was able to help otherwise.

edited to add: if you want to use your earlier attached files to associate fields to your "deduction agreement document" I'd be more than glad to see why it isn't updating when you filter the name of the patient, client, employee,etc..

**smasian** · 01-10-2013, 09:20 PM

Yes please!

It would make it a lot easier if all the links would update automatically. I've right clicked and changed all of them to "Automatically" for the updating link option, but it doesn't seem to work.

Please let me know what I need to do.

Thanks again! :D

**lilsnoop** · 01-10-2013, 11:34 PM

Hello smasian! I just wanted to confirm that you were following the steps I mentioned above, specifically that from the example excel spreadsheet I attached above that you were copying the data you needed on your "Word" document from the titled "Filtered" worksheet. This data changes when you filter the names on your "ABC" worksheet only. You would have to set up a similar formula situation for your "XYZ" sheet, which I did not do. I hope this makes sense.

Edited to add: I've attached your workbook again with the additional links for your XYZ sheet. It should open up on the sheet that you will need to use to copy the appropriate cells to your word document. Then in the future you should only need to filter the client name from whatever sheet (ABC or XYX) to have the info transfer to your Word document. Just remember that because you have more than one sheet with client names you should only filter one sheet at a time and when done return them to an unfiltered state... Best of luck and I hope I didn't confuse you.