Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 03-21-2013, 10:07 PM
Dsp581 Dsp581 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2013
Posts: 13
Dsp581 is on a distinguished road
Default make a form that can be used multiple times at once

Hi, I'm not very tech smart.. so don't know the proper words for what I want to say. I will try to describe this as best as I can. I will try to attach the forms that I am trying to work with. I made one spreadsheet that has the names of the people who took the orders of the products and the amounts that they ordered. The name of the "School" and their "packing #". The total they sold and the total packed including their free one, if they earned one. That spreadsheet, I don't have a problem with. The form I have a problem with, is the packing form. I would like all of that information to automatically go from the spreadsheet onto the packing sheet. But I can not figure out an easy way to do it. I did the first two. But I did them cell by cell. Sometimes I have 100+ names to enter and I am sure there must be an easier way to do this. Please Help!!! Thank you..Deb

I asked that question on an Excel forum last night and this was their answer..

Have you considered using Word and Mail Merge to do what you want..



Open Word
Setup a Form
Go to Mail Merge
Link to your Excel File
Insert fields
Merge
You can Merge to Individual Students, All Students and save as a combined File or Individual Files

While I appreciate them responding to me very fast.. I have absolutely NO idea what they are telling me to do. Starting with Setup a form. I know how to do labels and write letters in Word, and that's about it. I tried searching, but I didn't have any luck. Can someone Help me Please?? Thank you very much..Deb
Attached Files
File Type: xlsx 2013 packing sheet.xlsx (21.8 KB, 9 views)
Reply With Quote
  #2  
Old 03-22-2013, 12:00 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,664
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

For a mailmerge walk-through, see: http://office.microsoft.com/en-us/wo...001034920.aspx

From viewing your file, it would appear you can achieve your aim using a standard form letter merge, with one of your existing Excel tables pasted into Word (as a table), and the requisite mergefields added to it.

For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 03-22-2013, 12:13 PM
Dsp581 Dsp581 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2013
Posts: 13
Dsp581 is on a distinguished road
Default

Thank you for the link and fast reply. But I am still dumb! The 2013 packing tab is the one that I am trying to fill automatically for each student. When I go to the mail merge, it wants me to insert each of those fields. I don't understand how that is helping me with the auto-fill. Or how inserting those fields will make that form in Word look like it does in Excel. Is there a part that I am not getting there? When I try to paste my Excel spreadsheet onto the Word sheet, it is huge and way off the page and everything I have tried to reduce the size, has not worked. I have Word/Excel 2007. Thanks again..Deb
Reply With Quote
  #4  
Old 03-22-2013, 08:36 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,664
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Deb,

Once you insert the mergefields, the document is permanently set up for the packing slip production.

As for inserting the table into the document, the issue there is that your Excel pages are somewhat oversized.

The attachment gives you an idea of how the mailmerge will do. I had to make a few small changes to the workbook, by:
naming the mailmerge data range (Orders);
adding some more column names on row 2 (which is where the mailmerge data range starts); and
adding a column for the school names.

When you open the document, Word will ask whether it should run a query. Answer Yes. Word may then prompt you for the data source. If so, simply point it to the workbook. Then go to the Mailings tab and click on 'Preview Results'. You can then view each record. If you wanted to print just one record, you could do it from there. If you want to print the lot, click 'Finish and merge', from where you can create a single document containing all the records, or send them direct to the printer.
Attached Files
File Type: zip Packing Slips.zip (36.8 KB, 9 views)
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 03-22-2013, 10:44 PM
Dsp581 Dsp581 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2013
Posts: 13
Dsp581 is on a distinguished road
Default

Hi Paul,
I think I Might Love You
Thank you very much!
Deb
Reply With Quote
  #6  
Old 03-22-2013, 10:52 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,664
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Deb,

You're welcome.

Please advise the other Excel forum that the issue has been resolved, with a link to this thread. A Please also provide a link from here to your thread in the other Excel forum.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 03-23-2013, 02:42 PM
Dsp581 Dsp581 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2013
Posts: 13
Dsp581 is on a distinguished road
Default

Hi Paul,
Another question please... Is it possible to have two forms on one sheet of paper? With two different names? I put two forms on one sheet, but when I merged them, ex.. Bobby Brown's name was on both forms on sheet one and Johnny's name was on both forms on sheet two etc.. any way they can share the page, I don't need the entire sheet of paper for the packing sheet. Thank you again for making that form.
Deb
Reply With Quote
  #8  
Old 03-23-2013, 04:35 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,664
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Deb,

Yes, you can do that by inserting a 'NextRecord' field (from the 'Rules' dropdown on the Mailings tab) between the two forms.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 03-23-2013, 06:42 PM
Dsp581 Dsp581 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2013
Posts: 13
Dsp581 is on a distinguished road
Default

Excellent, Thank you Paul
I still have one more thing. On the form you made. For the CC totals, I need that total to include the Free one if they earned it. That total is under a column I named ccwf. I don't understand how to change the field from cc to ccwf, as that is not one of my options.
Deb
Attached Files
File Type: docx done word 2 form for packing 2 to a sheet.docx (38.7 KB, 4 views)
File Type: xlsx SCHOOL B new 2013 packing sheet.xlsx (15.0 KB, 3 views)
Reply With Quote
  #10  
Old 03-23-2013, 10:10 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,664
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

In that case, you need to add 'CCWF' to cell AA2 on the spreadsheet, then open the Word document and insert the 'CCWF' mergefield wherever you want it to appear.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 03-24-2013, 01:27 AM
Dsp581 Dsp581 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2013
Posts: 13
Dsp581 is on a distinguished road
Default

Thank you. I had tried that, I guess I needed to save it or close it or something. When I went back in it after reading your reply, the ccwf finally showed up.
Deb
Reply With Quote
  #12  
Old 03-24-2013, 01:59 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,664
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Deb,

Yes, you do need to save the Excel file - mailmerges only work with what's in a saved file. I also note from the workbook attached to your previous post that you seem to be trying to use separate workbooks for each school. With the proper setup, you can have records for multiple schools spanning multiple years all in the one workbook. Basically, all it requires is the application of some filtering.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #13  
Old 03-24-2013, 06:49 PM
Dsp581 Dsp581 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2013
Posts: 13
Dsp581 is on a distinguished road
Default

Thanks again Paul. I need to keep each school in their own folder so I need a spreadsheet and form for each school.
Deb
Reply With Quote
  #14  
Old 04-02-2013, 09:59 PM
Dsp581 Dsp581 is offline Windows 7 64bit Office 2007
Novice
 
Join Date: Mar 2013
Posts: 13
Dsp581 is on a distinguished road
Default

Hi Paul,
I have used the form a few times without any problems, but today when I went to print them out.. it printed 1 and 2... 4 and 5... 7 and 8 and so on. It took me several times going back and forth from the spreadsheet back to the form to get all of the 148 numbers printed. I checked in the recipients list and they were all checked to be merged. I went to the Rules and tried to find some way to remove the Next Record, but I did not see any way to do that.. can you tell me if there is a way so that I don't have to re-make the form over again each time I have a problem with it? Or is there some other easy fix that I can do to get every 3rd persons name to print? Thank you. Deb
Reply With Quote
  #15  
Old 04-02-2013, 10:04 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,664
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Hi Deb,

That suggests you have a second 'NextRecord' field, after the second set of mergefields. Delete that and everything should work OK. To do so, simply select the field and press the Delete key.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to make a form for my company, need a little help attredies Word 1 03-21-2013 11:18 PM
How can I make the default "paste" Size 12 Times New Roman? BrosephMG Word 1 09-28-2012 03:16 PM
Outlook send pdf files of large size multiple times ckiran04 Outlook 0 11-16-2011 04:49 AM
Outlook Sending Same Email Multiple Times mudpuppy Outlook 1 08-03-2010 06:39 PM
Outlook sends same message multiple times dikob Outlook 4 01-19-2009 12:13 PM


All times are GMT -7. The time now is 10:33 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft