One of the most enjoyable fringe benefits of writing a column for TechRepublic is receiving e-mail from Help Desk Advisor readers and subscribers to the companion Help Desk TechMail. I have to refer most technical questions to TechRepublic’s Technical Q&A section. However, as an Excel-ophile, I can’t resist the chance to be the spreadsheet hero; I try to respond to as many questions related to Excel as I can. This week, I’d like to share some of the Excel support questions raised in e-mail from TechRepublic members.

Nobody loves mail merge (but they should)
Many Excel users become adept at writing formulas and building labyrinthine spreadsheets, but cringe at the thought of using Excel as a data source for a mail merge in Word. I have long preached the gospel of using Excel—instead of Word’s built-in data source utility—to manage information used in Word’s mail merge. Even though that means users have to deal with two application interfaces instead of one, I contend it’s easier to maintain and share an Excel sheet than it is to maintain and share a Word data source.

I say as much every time I receive a note like the one I recently received from TechRepublic member Doris P., help desk coordinator for a New York City government agency. Doris wrote: “What do you have on mail merge in XP using an Excel spreadsheet as a data source?”

I sent Doris a link to the TechRepublic article “Word 2002’s mail merge: For better and worse.” When I receive a note from someone supporting Word 97 users, I send them a link to “The five-step guide to doing a mail merge with a form letter.”

No matter what version of Word is used, the Excel side of the mail-merge process is straightforward: You just have to label your columns of data, and the spreadsheet makes a great data source.

Data validation with a VLOOKUP twist
I knew I had a chance to be the hero when I received this note from TechRepublic member Jeremy W., MCP-MCSA and IT manager. After recently reading my article from April 2000 entitled “Ensure accurate data entry in Excel by using Data Validation to create drop-down lists,” Jeremy submitted this question:

“Is there a way to make it where a drop-down list is dynamically built? Example: I have two cells, B14 and C14. I want to make B14 a drop-down list with a static set of 5 values of metal. I want to make C14 show the available thicknesses available, depending on the metal chosen. So if ALUM was chosen from B14, then thicknesses for that value showed up in C14; if B14 was STEEL, then the thicknesses available would show up in C14. Could you please help me out?”

This kind of question comes up often when you train or support Excel users. It took about five minutes to set up the functionality Jeremy requested. Here are the steps to set up the data validation rule:

  • First, set up a table with the list of metal types in one column and the available lengths in another.
  • With cell B14 selected, go to Data | Validation | Settings.
  • In the Validation Criteria section, open the drop-down list for the Allow option and choose List.
  • Then, in the Source field, enter the range that contains the metal types, and click OK. In this case, the range is B7:B11.

Figure A shows what the worksheet looks like when I confirmed that the validation rule was working correctly.

Figure A
The data validation rule requires that the entry in B14 come from the list of names in the range B7:B11.

To display the available thicknesses for each metal type, I entered in cell C14 the formula =VLOOKUP(B14,B7:C11,2), which says, in English, “Take what’s in B14, look it up in the range B7:C11, and return the corresponding value from the second column in that range.” Figure B illustrates how this formula returns the available thicknesses for the selected metal type.

Figure B
The formula =VLOOKUP(B14,B7:C11,2) returns the appropriate value from the Thicknesses column based on the selection the user makes in cell B14.

When numbers won’t add up
I had an interesting e-mail thread with TechRepublic member Kelly H. regarding a column of numbers that wouldn’t add up. Kelly had copied and pasted two columns of data from a Web page. One column contained dates, and the other contained what appeared to be integers. However, no matter what Kelly tried, the Sum function wouldn’t add the values in the second column, and he couldn’t generate a graph based on the values in that column.

I asked Kelly all the usual questions for troubleshooting this kind of behavior:

  • Is the second column formatted as text? If so, try formatting it as General. Kelly had tried that.
  • Is there an apostrophe embedded in front of the numerals, causing Excel to treat them as text? Kelly said there were no apostrophes to be found.
  • Have you tried =VALUE(B2)? The Value function attempts to convert whatever is in B2 to its numeric equivalent, and that attempt resulted in an error message.
  • What about using =CLEAN(B2)? The Clean function is supposed to remove any “dirty” characters that might cause Excel not to recognize the entries as numbers. Unfortunately, the Clean function didn’t do any good, either.
  • Have you copied and pasted the data into a Notepad document? I thought no “dirty data” would survive being pasted in and then back out of Notepad. However, the column of numbers still wouldn’t compute.

Finally, I asked Kelly to send me a copy of the HTM page from which the data had been copied and pasted. Interestingly, when I viewed the source for that page, there were nonbreaking space characters (nbsp&) embedded behind the dates and the integers.

I pasted the data into a Word document, clicked the Show/Hide button to display hidden characters, and lo and behold, there were the nonbreaking spaces; they show up as degree symbols in Word, as shown in Figure C. I used the find-and-replace dialog to replace all of those spaces with nothing. Then, when I copied that “clean” data into the spreadsheet, Excel recognized the integers as values, and Kelly was able to sum and graph the data to his heart’s content.

Figure C
I copied the data into Word and replaced those nonbreaking spaces with nothing, which cleaned up the data so it could be used in Excel.

Between the spreadsheets

To comment on these Excel solutions, or to post a puzzler of your own, please post a comment or write to Jeff.