Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Something like Mail Merge (2000 SR-1)

    I have an Excel database with about 3000 names/addresses etc that I work with everyday. After I update the database, I use the Autofilter function to find the updated entries and copy them to another spreadsheet to do a mail merge and generate labels in Word. I'd like to use this same spreadsheet to fill in the cards that also need to be generated in Excel (attached spreadsheet) and was wondering how I go about doing that.

    Attached spreadsheet - The yellow fields on the first sheet need to be filled in with the appropriate info on the second sheet....

    Thanks much.
    Pooja

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Something like Mail Merge (2000 SR-1)

    You could create a macro if desired, but I chose formulas

    I named your data range:
    insert - Name - define
    Name: DataTable
    Refersto:
    <pre>=Info!$A$1:$I$11</pre>


    in B5:
    <pre>=INDEX(DataTable,(COLUMN()-2)/6+2+3*(ROW()-5)/13,1)</pre>


    In D5:
    <pre>=INDEX(DataTable,(COLUMN()-4)/6+2+3*(ROW()-5)/13,9)</pre>


    In B9
    =INDEX(DataTable,(COLUMN()-2)/6+2+3*(ROW()-9)/13,2)&" "&INDEX(DataTable,(COLUMN()-2)/6+2+3*(ROW()-9)/13,3)

    In B10:
    <pre>=INDEX(DataTable,(COLUMN()-2)/6+2+3*(ROW()-9)/13,4)</pre>


    In B11:
    =INDEX(DataTable,(COLUMN()-2)/6+2+3*(ROW()-9)/13,5)&", "&INDEX(DataTable,(COLUMN()-2)/6+2+3*(ROW()-9)/13,6)&" "&INDEX(DataTable,(COLUMN()-2)/6+2+3*(ROW()-9)/13,7)

    In B12:
    <pre>=INDEX(DataTable,(COLUMN()-2)/6+2+3*(ROW()-9)/13,8)</pre>


    You can copy the entire range B2:E13 to H2:K13 and to N2:Q13

    When you get more data rows in your database you can copy rows 2:13 to rows 15:26 and to 28:39 etc.

    You will get in first set Item 1,2,3 (B2:Q13) then 4,5,6 in B15:Q26, then 7,8,9 in B28:Q39, etc

    Is this what you are after? Or are you after a macro solution?

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something like Mail Merge (2000 SR-1)

    I'm going to attempt to apply the formulas to my real spreadsheets and see how what happens. If you could just let me know what to change in the formulas to take into account that the cards and the data are in two SEPARATE files I'd really appreciate it. I''m sure you can tell I'm not that much of an expert....

    Thanks again!
    Pooja

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Something like Mail Merge (2000 SR-1)

    when you create the name(insert - name) in the workbook with the cards, in the refers to type and equal(=) then select the range in the other workbook (with the data)
    The refers to will look something like this:
    <pre>='[Data File name.xls]Info!'$A$1:$I$11</pre>


    When the data file is open or when the file is closed like this:
    <pre>='Cath[Data File name.xls]Info!'$A$1:$I$11</pre>


    No other changes need to be made. This will create a link the cards to the data. The data file does not have to be open.

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Something like Mail Merge (2000 SR-1)

    Just wanted to say THANKS! You solved my problem.

    Pooja

Posting Permissions

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