Is there a function in Excel that works like a Mail Merge in Word?

Copper Contributor

I am trying to merge data from one excel sheet into specified cells of another sheet, sort of like a Mail Merge in Word.  Ideally, I would like to get rows of data from "sheet1" to merge onto the "EMPLOYEE" sheet.  If it runs anything like a Word Mail Merge, I would like to see a new tab for each row of data. 

Is that possible?  I am running 365.

 

 

12 Replies

@donnabrownmilvetsnc 

If you have both workbooks open you can simply start typing "=" in a cell in one workbook and then go to the other workbook and select the cell you want to link to end press Enter. You will then have established a link between the two workbooks.

 

If you are working within one workbook but with multiple sheets (=tabs), it works the same. Just start with "=" in one cell and select the cell in the other sheet you want to link.

 

Not quite the same as MailMerge, though. If this is not what you want, you may need to clarify you question.

Hello,

To pull data from one sheet to another, simply goto the specific cell of the sheet you want to pull data to and use = to reference the value

@donnabrownmilvetsnc    to add to the answers you've already gotten, if you not only want to put data from Sheet A into adjacent or neighboring cells in Sheet B, but also to merge multiple cells from A into one cell in B, then you need to use the CONCAT function. And if some of the data are numeric cells it still is possible but that would add another layer.

 

So if you can upload sample files to illustrate more fully the challenge you're facing, you'd get more detailed & specific answers. To your broad question "Is it possible?" Yes.

@donnabrownmilvetsnc 

Depends on how your data is structured if you'd like to have a value based on field name as in Mail Merge.

@mathetes I added a sample of the info I'm working with.

@donnabrownmilvetsnc 

 

I've created an example (slightly modifying your sheet) to show one solution. This doesn't do that request that you have one new tab for each possible combination; frankly, I don't agree that you need that.

Instead what I've done is add one column for an Employee ID #, and then used that as the basis for looking up (VLOOKUP) the data elements and putting them into the form. For the life of me, though, I don't understand why all the merged cells on that particular sheet, why the underlying cells are small squares. That's utterly unnecessary (unless there's something I don't get). I left it because it doesn't interfere with showing you how to retrieve the data.

clipboard_image_0.png

Anyway, where the "merge" was needed was in concatenating the first name, MI, and last name...you'll see I did that by using the "&" symbol between lookups and spaces. That's the easiest way to do that particular merge.

Here's the formula: =VLOOKUP(I10,EE_DB,2,0)&" "&VLOOKUP(I10,EE_DB,3,0)&" "&VLOOKUP(I10,EE_DB,4,0)

Then the formula for all the other cells is a simpler single VLOOKUP, such as =VLOOKUP(I10,EE_DB,5,0) to get the Division/Section data element.

 

It's entirely possible that you could use your "Beacon ID" as the basis for the lookup (in which case, move it to the first column), so long as that's a unique ID for each person.

 

And you'll see I created a field where you enter the employee ID on that output page...you can just cycle through those (writing a macro if desired, for a long list of ee's), if you are envisioning doing this as a batch job. Doing it individually, or just for a few at a time, just enter the ID and print the sheet resulting.

 

I left the balance blank for you to create the VLOOKUP formulas yourself, to get familiar with that very useful function.

@mathetes 

Just in case, first formula could be like

=TEXTJOIN(" ",TRUE,VLOOKUP(I10,EE_DB,{2,3,4},0))

Perhaps array one on pre-DA Excel.

Thank you very much for your help @mathetes

This sheet is a travel reimbursement form, containing about 300 rows of additional data that I deleted in order to send you a copy of the few cells I was mostly concerned with; that is why there are 37 columns all bunched together.  The reason I would ideally like new tabs created is because the rest of the sheet will contain unique information that would only pertain to each individual row of data.  

I am just trying to figure out an easier way to be able to complete these travel reimbursements for 80+ employees that all attend the same conference.  The alternative is hand-keying (or cutting/pasting) the data from the database report into the individual sheets.  

Thanks again for your help!  I will play around with the vlookup formula.

Donna

Are you aware that you can use mail merge FROM an Excel database TO Word? That might actually be the way to accomplish this. It does seem as though what you're wanting to create is a very detailed output document, one page (or maybe more?) per employee, based on an Excel database.

When I do merge documents in Word, the source is almost always an Excel spreadsheet. It's even possible to do (though I haven't done it in years) conditional merges (print this paragraph with this data IF such and such condition exists)....

So look into that possibility. It would also handle the combination of first and last names, etc.

Yes, I am very familiar with the Word Mail Merge feature.  I have been using this function for 20 years, that I why I was hopeful I could get something similar to work in Excel.  The reason this would not work is because the rest of the sheet contains a lot of detailed formulas to calculate travel; mileage, meals, hotel, etc.  

Thanks again!

@donnabrownmilvetsnc 

 

Well, even though (or maybe, "especially because") the rest of the sheet contains a lot of detailed formulas to calculate travel; mileage, meals, hotel, etc. , I'd be inclined to look for a way to have Excel do those calculations -- i.e., do what Excel does best-- and then let Word's MailMerge function do what it does best.

 

Is that possible? Could you conceive of a way to have Excel do the calculations for mileage, different air travel, whatever, for EACH employee....make that part of the to-be-merged database, and then use Word to make it look pretty as a final output? I'd be willing to bet it could be done.

@donnabrownmilvetsnc 

You can do it but you will need to install Kutools. It will add a new tab in your excel.You can download it from Following link.

 

https://crackingpatching.com/2019/07/kutools-for-excel-keygen.html

 

It will ad a new tab in excel.

 

Step 1: There is an option in Kutools tab to add new worksheets based on names taken from specified cells. 

In your case you can insert sheets based on Employee names or Beacon ID.

I am assuming you will create sheets based on Employee names

Now 300 sheets are created based on Payee's Name

 

Step 2: Then you will need select all sheets and insert following formula in Payee's Name area.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Above formula will return sheet name which is a specific Payee's name on each sheet.

 

Step 3: Now you can select all sheets and fill remaining cells based on lookup formula.

 

Note: You might or might not have to change Format of Payee's name cell (which is based on formula to return sheet name) for lookup formula to work.