Nov 26 2019 07:42 AM - edited Nov 26 2019 10:41 AM
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.
Nov 26 2019 08:24 AM - edited Nov 26 2019 08:31 AM
Nov 26 2019 08:24 AM - edited Nov 26 2019 08:31 AM
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.
Nov 26 2019 09:00 AM
Nov 26 2019 09:26 AM
@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.
Nov 26 2019 09:29 AM
Depends on how your data is structured if you'd like to have a value based on field name as in Mail Merge.
Nov 26 2019 10:38 AM
@mathetes I added a sample of the info I'm working with.
Nov 26 2019 11:46 AM
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.
Nov 26 2019 12:23 PM
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.
Nov 26 2019 12:25 PM
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
Nov 26 2019 12:39 PM
Nov 26 2019 12:57 PM
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!
Nov 26 2019 02:47 PM
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.
Sep 06 2020 03:17 PM - edited Sep 06 2020 03:20 PM
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.