Feb 07 2021 02:54 PM - edited Feb 07 2021 03:09 PM
Hello, Can we do something in Excel that's similar to a mail merge in Word. I have a spreadsheet that is essentially a form letter in Excel format, contains empty fields which I want to fill with the the records from another spreadsheet which has information i.e., For example, first name, last name, employee id, marks, etc. Let's say I have 50 records in the second spreadsheet. The goal is to make 50 versions of the first spreadsheet (the form letter), each filled out with the info of a given record (or row) from the second spreadsheet.
Also, whenever a new record is added in workbook 1(data sheet) I want to see same data in workbook 2(form sheet).
Thank you very much in Advance for your help!
Feb 07 2021 05:16 PM
Well, my help will come in the form of questions.
First, what is it that you are really looking to accomplish at the end of all this? Do you really need 50+ copies of the same spreadsheet, each populated by one of 50+ different records? That doesn't sound very efficient. If you're really creating a form letter (as opposed to just using that as an analog) then Word's Mail Merge, with Excel as the data source, would be the way to go.
If, on the other hand, you need to show these records for some reason in an Excel format (if, that is, the row and column set up is integral with your goal) then, yes, it would be fairly easy to do using one of several LOOKUP functions or INDEX and MATCH as the basis for extracting the various fields and populating the target sheet. But what, I wonder, is that reason? Why not just create them one at a time as needed?
I (for one) would first be wanting to know why you want to populate 50+ individual sheets with all that data rather than continue to build a single database that can at any time be used to populate another sheet for a nice looking printout. What are you proposing to do with the individualized (or mail-merged) sheets once they're created?
I ask these questions not to be impertinent, but because in my experience with Excel and with seeing others use it, I'd say it's not uncommon that people will create a lot of individual records that end up actually getting in the way of really using Excel well. So I apologize but I feel responsible for trying to ensure that you're using Excel well.
Feb 07 2021 07:55 PM
Feb 08 2021 08:06 AM
There still are a few lingering question (doing this by exchanged messages is so much harder than in a face-to-face meeting!) at least there are questions dangling in my mind.
Now, all of those questions having been asked, I can envision at several scenarios, depending on your answers to the above.
All of which is to say, I still don't have the bigger picture here and am wanting to get there. My questions are not meant to be difficult--however they might be perceived--but to get us to the point where we do can achieve the most functional approach to these "report cards."