Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combine Many Sheets

    I am looking for the simplest way to combine several copies of the same worksheet (see attachment). The real sheet has many more sets of names to be voted on. Each voter will be filling out one line for each name. After receiving the sheets back from the voters, we will want everything voter 1 entered (for each name) copied into the master sheet. Likewise for voter 2 etc.

    For this instance, using routing recipients won't work.

    The simple answer is probably to put the copies of the sheets in one workbook and then enter formulas in the master. Simple for me, not for the person using the sheet who wants to open 2 windows and reenter the data. I was hoping for a simpler solution?
    Attached Files Attached Files
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Many Sheets

    Well, if I were doing this, and if I usnderstand what is being done, I would change the way it is being done just a little. I would redesign the sheets being sent to the voters and have only one line per name (drop the (Voter 1)), and I would name those workbooks Voter 1.xls, etc.

    Then, I would change the master sheet to put the Name 1 and Voter 1 in separate columns. Then I would put all of the Voter *.xls sheets in one directory and write a macro in the master sheet to open all of the files in that directory and copy the Name * lines from them to the end of the master sheet, and insert the Voter * from the file name into its column. The macro would then sort the master sheet on the name and voter columns to get it in the order you want.

    A macro could work with what you have now, but it would be a bit more difficult. You have the problem of finding which line the voter voted on, and deciding what to do if he used Voter 1 for Name 1 but Voter 2 for Name 2. You also have the problem of what to do if the master sheet already contains data in the line when the macro goes to add more data. Why are all those extra lines being sent to the voters anyhow, it looks like it is asking for errors (especially if it is sent to Florida).
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combine Many Sheets

    Thanks Legare,

    Your post made me look at it differently and I was able to convince the user to redesign the spreadsheet.

    Thanks again, <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •