Forum Discussion
How to generate Automatic form in excel (mail merge)?
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!
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.
- Thank you, for your reply, Mathetes!
Step-1:
I have 50 rows in a spreadsheet-1 With a unique column, based on that I want to split data into 50 different spreadsheet-2 (form-based excel). So we will have 50 sheets that will be unique values.
Consider it as a school report card with 50 students, I need to generate a report for each student.
Also, I need to generate a report card every time whenever a new student has joined the class and also weekly basis (any new update data will be saved in workbook -1) based on workbook-1 I need to generate workbook-2 for each new row.
Step-2:
Do we have an option in excel where once workbook 1 is updated and if we click on some button automatically workbook 2 will be generated?
I can do step-1 in word mail merge but I need Step-2 (Automating the workbook 1 to workbook 2 whenever a new record is added)
This may sound very lengthy, please help me out! thank you again in advance for your response.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.
- Once you have all 50+ report cards, are they static?
- OR: Do they update each week with new data in the master database, and need to be printed or otherwise distributed on a regular basis?
- Do these weekly "report cards" get used themselves as the basis for additional data entry?
- Is there anything else you can describe here to give a better understanding of what happens next WITH the individual records
Now, all of those questions having been asked, I can envision at several scenarios, depending on your answers to the above.
- If in fact the 50+ report cards, once generated, are basically static--don't change themselves--then it would be easiest, I'd think, to just create a "dashboard" kind of sheet, formatted as you would for utility and clarity, a sheet that uses one of the several LOOKUP functions to populate the various fields, and run through them manually once, printing them out.
- If, on the other hand, you're updating the master database each week (or some regular and ongoing basis), then a macro could be written (by somebody else; it's been too long since I did that, although I have, but maybe 25 years ago), a macro that would run through the database and print each one anew each time they're updated
- If you really just want them in Excel (not printed), and for some reason still not clear to me want all 50+ to be each on their own tab, used only to look at...{ well, if it hasn't been clear yet, I question that; it's just so easy to pull up as needed any one of them in a single sheet designed specifically for that purpose
- UNLESS you're actually using all 50+ to collect individualized data on them rather than in the master database. And if that's the case, I'd question that too;
- it's always cleaner to collect (Input) data in the database;
- and display it in some Output sheet (such as the aforementioned "dashboard")
- UNLESS you're actually using all 50+ to collect individualized data on them rather than in the master database. And if that's the case, I'd question that too;
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."
