Forum Discussion
Is there a function in Excel that works like a Mail Merge in Word?
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
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.
Depends on how your data is structured if you'd like to have a value based on field name as in Mail Merge.
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.
mathetes I added a sample of the info I'm working with.
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.
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.
- 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 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.
