+ Post New Thread
Results 1 to 2 of 2
Office Software Thread, Mail Merge Issues (Still) in Technical; So I currently trying to get an overly complicated mail merge out of SIMS. It's overly complicated becuase I need ...
  1. #1

    Join Date
    Nov 2007
    Location
    Rotherham
    Posts
    1,675
    Thank Post
    122
    Thanked 126 Times in 102 Posts
    Rep Power
    45

    Mail Merge Issues (Still)

    So I currently trying to get an overly complicated mail merge out of SIMS. It's overly complicated becuase I need to include the initals of each students middle names (if they have any) So without creating a UDF with that info in I'm a bit stuck unless I can flush it through Excel. The good news is that I can flush it through excel and into word and keep everything reasonably automated. My problem however is this.

    As the length of each list I need will change I need to have formula's in many cells. However my mail merge's all detect that there is data in the cells even though the value is blank ( A1="" for example). Even if I copy and Paste Values into a new worksheet the mail merge still thinks there is data and puts in blank records which muck everything up.

    I've saved my spreadsheets as CSV Files to see what they look like and there are additional lines coresponding to the rows that have formula's in but no data.

    Anyone know a way to get round this behaviour?

    A side question, anyone know a way to return the cell reference of the last value in a column to contain data?

  2. #2

    Join Date
    Oct 2010
    Posts
    23
    Thank Post
    6
    Thanked 1 Time in 1 Post
    Blog Entries
    2
    Rep Power
    42

    Hopefully this helps...

    My first post

    Fixing the blank cells that aren't really blank...

    • Convert the formulas to values using copy and paste as values (or similar) as you've already done.
    • Select the column of data and go to the Data tab on the ribbon.
    • Select "Text to Columns" - whatever you choose as your delimiter should not appear in your data, otherwise multiple columns will be created.


    N.B. You can only use "Text to Columns" on one column of data at a time.

    Finding the last used row in a column of data...

    I am not sure how robust the following is as a solution, but it might do the trick:

    • Suppose you are looking at column I and the text "ZZZ" is greater than any of the data in that column. (I'm assuming you have text and not numbers; just substitute a number if that's appropriate.)
    • The following formula should give the last used row: =MATCH("ZZZ", $I:$I, 1)


    If it's easier to think of a value smaller than all the values in the column, then try =MATCH("a", $I:$I, -1) instead, where "a" is less than all the values in the column. In either case, you might need to choose carefully if you still have the pesky blank-cells-that-aren't-really-blank problem.

    If you want the actual cell reference, then you can use string concatenation to get the row reference, e.g. ="I"&MATCH("ZZZ", $I:$I, 1).

    If you want the value of the cell, then wrap INDIRECT around it: =INDIRECT("I"&MATCH("ZZZ", $I:$I, 1)).

    Hopefully, you've already solved it.

SHARE:
+ Post New Thread

Similar Threads

  1. [MS Office - 2010] Edit Mail Merge Fileds
    By Stuart_C in forum Office Software
    Replies: 0
    Last Post: 25th November 2010, 05:31 PM
  2. [MS Office - 2003] Email \ Mail merge
    By User3204 in forum Office Software
    Replies: 1
    Last Post: 11th November 2009, 08:37 PM
  3. Mail merge and pictures
    By GrumbleDook in forum Educational Software
    Replies: 11
    Last Post: 4th February 2009, 11:13 AM
  4. SIms mail merge
    By KWestos in forum MIS Systems
    Replies: 1
    Last Post: 2nd October 2008, 07:31 PM
  5. Mail Merge
    By Teaboy in forum MIS Systems
    Replies: 4
    Last Post: 12th November 2007, 06:32 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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