Forum Discussion
Is there a function in Excel that works like a Mail Merge in Word?
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.
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!
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.
