How to Correct #N/A Error Messages for Excel’s VLOOKUP Function

Fix the #N/A Error Message

A colleague of mine – who is an experienced Excel user – cannot figure out why he is receiving those ugly #N/A Error Messages when he is using the VLOOKUP Function in Excel.

After a quick look at his workbook, I spotted the problem!

Use the TRIM() Function to Remove Extra Spaces

It turns out, that the data source – housed on the company’s Main Frame Computer – is using “Fixed Width” Fields. So, while only 7 characters are visible, I used the LEN() Function to determine that the field was actually storing 12 characters.

The #N/A Error Message for VLOOKUP()

The #N/A Error Message appears when a Match cannot be found for the Lookup Value in the Left-most Column of the Table_Array when using VLOOKUP(). In this case, my viewer was trying to match a lookup value that contains 7 characters to a value in a field formatted to contain 12 characters. This is what produced the #N/A Error!

Paste Special Values

The final step to correct this problem is to use Copy – Paste Special – Values to replace the Formulas with the values of those formulas.

Essential Excel Skills

9 Essential Skills for Excel 2010

9 Essential Skills for Excel 2010 Video Tutorial

In this video, I cover three Essential Excel Skills:

  1. Essential Functions – VLOOKUP()
  2. Text Functions – LEN() and TRIM()
  3. Paste Special Options

I cover each of these skills – and more – in greater detail on my new 4-Hour Video Training Resource, “Nine Essential Skills for Excel.”” Click on the links below to learn more about:

Watch Video in High Definition

Follow this link to watch my Excel tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Write an Excel Macro in VBA to Update Worksheet Tab Name

Characters Not Allowed in Worksheet Names

Characters Not Allowed in Worksheet Names

This is Part 2 of my two-part series of video tutorials where I demonstrate how to link an Excel Worksheet Tab Name and the contents of a Worksheet Cell.

In this lesson, I show you how to write an Excel Macro in VBA (Visual Basic for Applications) that will update the name of the Worksheet Tab based on the value of a cell in that worksheet.

Write Excel Macro in VBA

For this Macro, the first line of code will prevent the Macro from crashing if the cell contains one of the “disallowed” characters that you see in the diagram

On Error Resume Next

The second line of code initiates a “For – Next” loop of instruction

For Each ws In Thisworkbook.Worksheets

The third line of code is the Instruction to follow in the “For – Next” Loop

ws.Name = Left(ws.Cells(1,5).Value, 31)

Here, this instruction is saying – in plain English – “Nane this Worksheet (ws) using the leftmost 31 characters in the cell that is in the 1st row of the 5th column – (Cell E1)”

The reason for including 31, is that that is the maximum number of characters allowed in an Excel Worksheet Tab Name.

The fourth and fifth lines of code complete the macro – watch the video to see what they are (What a “tease” I am!!)

Run Macro from Command Button

In this lesson I also show you how to run this Macro from a command button that I add to the Quick Access Toolbar in Excel 2007 or Excel 2010.

Watch Video in High Definition on YouTube

 Click on this link to watch this Excel Video Tutorial in High Definition on my YouTube Channel – DannyRocksExcels

Source for This Code

I found the code for this example in one of Bill Jelen, Mr. Excel’s Books, “Excel Gurus Gone Wild.”

Bill’s website is: http://www.mrexcel.com/

Watch Part 1 of this Series

Here is the link to Part 1 of this series. I show you how to write a Custom Excel Function to insert the Name of the Worksheet Tab into a cell on the worksheet.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

How to Use Text Functions in Excel

Text Functions in Excel

Text Functions

In my experience, I find that many of my clients do not realize that Excel has many powerful – and easy to use – Text Functions.

Even more amazing, I observe clients spending hundreds of hours and thousands of dollars “re-keying” or “manually editing” text entries that have been downloaded from a Main Frame Computer. If you – or your colleagues – have been doing this, you will want to watch this Excel Video Tutorial.

Categories of Text Functions in Excel

In this Excel video lesson, I demonstrate how to use three categories of Text Functions:

  1. Transform Text – Use the UPPER, lower and Proper functions to change the “Case” of a text string.
  2. Join Text – Use either the CONCATENATE() Function or the & (Ampersand) Operator to join two or more text strings into a single text string.
  3. Clean-up Text – Use the LEN(), TRIM() and CLEAN() Text Functions to “trim the fat” from text strings – especially those that you download from a Main Frame Computer.

Once again, in my experience, I have too many clients contacting me in a panic because they get a REF# error when using Text Functions. I demonstrate how to recover from a “seeming disaster” and also how to use COPY – PASTE SPECIAL – VALUES to transform Formulas into Constant Values.

I welcome your feedback. Please send me an email: danny@thecompanyrocks.com – to let me know if this tip helped you to save time and money. I also welcome your suggestions for future Excel Video Lessons to create and post here, on my website.

Watch This Excel Video Tutorial in High Definition Mode

Follow this link to view this Excel Video Lesson in High Defintion Mode on my YouTube Channel – DannyRocksExcels

Download the Excel Workbook

Follow this link to download the Excel Workbook I use in this lesson.

Learn How to Master Excel in Minutes – Not Months!

If you like this Excel Video Tutorial, I invite you to learn more about the resources that I offer to help you to improve your Excel skill set.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

How to Search For and Use Text Strings in Excel

A viewer asked for my help in creating a report that will group his customers’ email addresses by their “domain name.” For example, he wants to be group all customers with gmail addresses . Likewise, his  customers’ yahoo mail, aol.com accounts, etc.

Pivot Table for Email Domains

Pivot Table Email Domains

I immediately decided to use two powerful Excel tools to produce this report:

  1. Text-to-Columns to create two columns (name and domain) from one text value (the email address).
  2. Pivot Table Report to group and present customer emails by domain name.

Both tools work great and I produced the report with only a few mouse clicks!

The response that I got from my viewer was, “That’s great Danny. But …”

It turns out that the viewer wanted to have the Domain Names as the Field Headers and to show each customer’s email address as a row going down vertically in the corresponding field. To give my viewer the report that he wanted, I used these tools and techniques:

  • I returned to the Pivot Table to copy the unique list of domain names.
  • I used Paste Special – Transpose to convert the vertical list of domain names into a horizontal row of Field Headers.
  • I began to build my “Mega-formula” by taking “baby steps” – i.e. I created one formula and then “nested” it inside additional formulas and functions.
  • For the first “baby step” formula I used the SEARCH() Function.
  • Next, I “nested” this formula as the “logical test” inside an IF() Function.
  • In order to “hide” all of the formula error signs, I nested both of these functions inside the NEW IFERROR() Function.
  • IFERROR() was introduced in Excel 2007 and I now consider it to be “indispensable!” This function, alone, makes upgrading to either Excel 2007 or Excel 2010 a “nobrainer” decision!

I enjoy answering questions from my viewers. Many of these questions can be answered from one of the lessons on my DVD-ROM, “The 50 Best Tips for Excel 2007.” For the rest, I try to create a video lesson that demonstrates how to get the answers that you – my viewers and customers – are looking for.

Keep your questions and suggestions coming! I enjoy receiving feedback from you!

I invite you to subscribe to my RSS Feed on Feedburner to be automatically notified whenever I publish a new Excel Video Tutorial.

Watch this Excel Video in High Definition on YouTube

Here is the link to watch this video on my YouTube Channel – DannyRocksExcels

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Use Excel’s Text-to-Columns Tool to Quickly Correct Date Entries

The Text-to-Columns tool in Excel is one of my favorites. I use it to quickly “parse” text elements in a cell. For example, to separate “First Name” from Last Name in a cell that contains the “Full Name” entry.

In this short – 3 minute, 23 seconds – video, I demonstrate another way to use Text-to-Columns. I show you how to separate the “Year,” the “Month” and the “Day.” A client called me in a panic – they downloaded daily transactions from their mainframe computer into Excel. However, the “dates” appeared as – e.g. 20100901 – and they could not find a way to format the dates as – e.g. 9/1/2010

Learn how to “Master Excel in Minutes – Not Months!”

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

I Have Updated My Index of Excel Video Lessons

As part of the process of reorganizing my website, I have updated my “Index of Excel Video Lessons” page. The content of this page will be updated as I add each new Excel Video Training Lesson. So, I suggest that you click here to go to the Video Index and then save the page as one of your “Favorites” if you want to learn how to really get the most out of Excel or to find a quick answer when you are using Excel.

On the Index Page you can see an organized listing of my Excel Videos by Category – e.g. Formulas, Formatting, Pivot Tables “What-if” Analysis, etc. Each lesson is “hyperlinked” so that you can just click on the topic to go directly to the lesson that you are interested in.

I indicate in the title if the lesson is specific to Excel 2007.  All my new Excel video lessons are created using Excel 2007.

If there is a topic that you would like me to cover, send me an e-mail message – danny@thecompanyrocks.com

If you enjoy my video instruction, I encourage you to purchase my DVD, “The 50 Best Tips for Excel 2007.” The DVD contains over 5 1/2 hours of Tips, Tricks & Techniques to help you to master the program – and to save a considerable amount of time while working in Excel! Click here to enter my secure shopping site.

Thank you for your support and encouragement!

Danny Rocks

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

How to Prepare Your Zip Codes for Mailing Labels

Clients call me up in a panic. “Danny, we are sending out a direct mail piece and Excel has screwed up our Zip Codes – they only contain 4 numbers! The Post Office can’t deliver the mailing piece this way. Can you help us?”

Yes I can!

The problem is not with Excel. The problem is not choosing the correct formatting for the Columns / Fields that contain Zip Codes or Postal Codes.

At the risk of jeopardizing some lucrative consulting fees, I will demonstrate how to solve this problem in this short Excel Video Tutorial.

Here are the steps to follow in this Excel Video Lesson:

  1. When you have control over your data, pre-format your Zip Codes column using the “Formant Cells, Number, Special, Zip Code” format.
  2. If your data contains cells with both 4 and 5 digit Zip Codes, use this formula:
  3. =IF(A2<=9999, 0&A2, A2)
  4. Now, you will see some cells formatted as “Text” – aligned to the Left side of the cell and other cells formatted as “Numbers” – aligned to the Right side. Take the next step:
  5. First, Copy the data (the cells w/ the =IF() Function) and choose Paste Special, Values to return the results of the formulas.
  6. With the cells still selected choose, Data, Text-to-Columns and choose “Fixed Length.” After previewing your data, choose the “Format as Text”  button and Finish the Wizard.

Find the Excel Training Video that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Learn how to convert text in a cell into multiple columns

Here are the steps to follow in this lesson:

  1. Highlight the cells containing the text that you wish to extract.
  2. From the Data menu, choose “Text to Columns” and follow the Wizard.
  3. Choose the correct “Delimiter,” e.g. “Space” or “Tab.”
  4. Preview the result and click Finish.
  5. Cut and paste as needed.

Find the video lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Related Videos

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Learn to extract information from a text string

Here are the steps to follow in this lesson:

  1. Use the =LEFT() Function to extract characters at the beginning of a text string.
  2. Use the =RIGHT() Function to extract characters at the end of a text string.
  3. Be sure to distinguish “stored cell” entries from “formatted” or “displayed” cell values.
  4. Before you use the =MID() Function to extract characters in the middle of a text string, be sure to locate the starting location.
  5. The =MID() Function requires 3 arguments.

Find the video lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Use the proper text function to change the case

Here are the steps to follow in this lesson:

  1. Use the =PROPER() function to convert your text string.
  2. Look out for names like “Mckinley” – You will need to edit these cells manually by:
  3. Changing the cells from Formulas to Values using Copy, Paste Special, Values. Then edit each cell.
  4. Before deleting a column of data, be sure to change the cells from Formulas to Values.

Find the video lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn