[ms office - 2010] Mail Merge Issues (Still)

[Stuart_C]

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?

[Pico]

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.