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...
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.
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."