Home Archives for Named Cells and Ranges
November 30, 2010 9 Comments

How to Name a Large Range of Non-Contiguous Cells in Excel

A client asked for my help on this problem. How to create a Named Range in Excel for Non-Contiguous Cells. The problem is that Excel puts a 255 character limit on the “Refers to” portion of a Named Range. I struggled with this until I found the answer in one of my favorite resource books, “This isn’t s Excel, it’s Magic!” written by Microsoft MVP (Most Valued Professional) Bob Umlas.

Solve the Problem

  1. Select the non-contiguous cells that you want to name.
  2. Open up the Visual Basic Environment (Alt + F11 is a Keyboard Shortcut).
  3. In the VBE, open up the “Immediate Pane” – Ctrl + G is the Keyboard Shortcut.
  4. Finally, type Selection.Name = “MyLabels” in the Immediate Pane and press Enter. (Substitute the name you want for “MyLabels”).

When you return to Excel, you will see that you can use the Drop-down list in the Name Box or in the Excel 2007 Name Manager to select this range.

 Amazing! Thanks Bob!

Additional  tips

  • Keyboard Shortcut F5 to open up the “Go To” Dialog Box. Select “Special” and choose “Constants – Text” to select the non-contiguous Labels that you want to name.
  • Right-click the worksheet tab and select “View Code” top open up the VBE (Visual Basic Environment) window.

I invite you to watch and subscribe to my Video Podcast on iTunes – follow this link to “Danny Rocks Tips and Timesavers”

Click here to watch this video in High Definition, Full Screen Mode on my YouTube Channel – Danny Rocks Excels

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

How to Name a Large Range of Non-contiguous Cells in Excel [ 4:49 ] Play Now | Play in Popup | Download (788)
Link to this post!
Filed Under: Excel Training, Names for Cells and Ranges, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , ,
November 27, 2010 2 Comments

How to Use the Indirect Function to Create Excel Dashboards

You can use the Indirect Function to create an Excel Dashboard. A Dashboard gives you an overview of your data – in this case, an interactive view of the information hidden in our data set. I will create Drop-Down lists using Data Validation in the Dashboard. The Indirect Function takes a little bit of time to understand. Here are some key points:

  • The Indirect Function works best with Named Cell References
  • The syntax is =INDIRECT(reference text). In our example the “reference text” will be the label for a Named Range of cells.
  • You can “nest” the Indirect Function inside another function – e.g. =SUM(INDIRECT())

Later in the lesson, I show you how you can quickly  look up any cell at the “intersection” of a Named Row and a Named Column – e.g. April East. You use the SPACE BAR as the “Intersection Operator” to point to the cell where “April” meets “East.”

50 Best Tips for Excel 2007

Excel Tips

Some of the Keyboard Shortcuts that I use in this lesson are:

  • Ctrl + A to select all of the cells in a Data Set
  • F3 to bring up a list of all the Named Ranges that have been created in the current workbook
  • Ctrl + Shift + F3 to create Named Ranges for the cells in the selection – using as Names, the Labels in the Top Row / Left Column

Here is a link to the 1st lesson in this series on the INDIRECT Function in Excel.

Follow this link to watch this lesson in High Definition, Full Screen Mode on YouTube.

You can learn how to “Master Excel in Minutes – Not Months!”

How to Use the Indirect Function to Create Excel Dashboards [ 7:20 ] Play Now | Play in Popup | Download (1168)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , ,
November 25, 2010 3 Comments

How to Trigger a Unique List of Values Based Upon Another Drop-Down List in Excel

You can use the INDIRECT() Function in Excel to create a drop-down list of unique values that is“triggered” by the label that you select in another drop-down list. In the scenario that I use in this lesson:

  1. Create your 1st Drop-down list using Data Validation in Excel – Allow “List.” Here the named range is “Departments.”
  2. Create a 2nd Drop-down list using Data Validation. This time the “list” uses the INDIRECT Function to reference the selected label from the 1st drop-down list.

Key Concept

 The labels in my 1st drop-down list serve as “named range” references. That is why the INDIRECT Function is able to pull up the correct list of “Job Titles” in the 2nd Drop-down list. For example, a reference to the text label “Sales” in the 1st drop-down list brings up a list of the values (Job Titles) from the “Named Range” (Sales) in the 2nd Drop-down list.

If you need to learn about – or review – Data Validation in Excel, follow this link to my free Excel video lesson on this topic.

Create Named Ranges in Excel

I have found that creating and using “Named Cell Range” references makes lookup functions and formulas so much easier in Excel. Watch this video to see how I use a variety of methods to Create Named Ranges in Excel. One tip is to use the Keyboard Shortcut “Ctrl + Shift + F3” – after you first select the Label and the Values for your Named Range.

In this lesson, I also demonstrate how to visually “flag” all of the cells that do not meet the criteria for your Data Validation. I find that most people are unaware that you can do this! I think that you will find many uses for this auditing tool as you work in Excel.

Additional Resources

Follow this link to watch this video on YouTube in High Definition, Full Screen Mode.

Here is the link to view the 2nd video in this series about the INDIRECT Function in Excel.

Learn how to “Master Excel in Minutes – Not Months!” – visit my new, secure shopping site – http://shop.thecompanyrocks.com

How to Trigger a Unique List of Values Based on Your 1st Drop-Down List [ 6:23 ] Play Now | Play in Popup | Download (1046)
Link to this post!
Filed Under: Data Validation, Excel Training, Lookup Functions in Excel, Names for Cells and Ranges, Podcast Tips & Timesavers Tagged: , , , , , , , , ,
October 28, 2010 13 Comments

How to Add a Combo Box Control to an Invoice in Excel

If you are creating or modifying an Invoice Form in Excel, you will want to add a Combo Box Control to allow users to select products from a listing. The key in formatting your Combo Box Control is to choose the correct cell to contain the “Cell Link.”

Cell Link in Form Control

I think that you will benefit from “seeing how this is done” in this video lesson. I know that I always struggled with “reading about” Excel Form Controls. Once you see how important the “Cell Link” placement is, you will better understand how Combo Box Controls work.

Use INDEX Function

Once we have formatted the Combo Box, we need to be able to look up other values to place on our Invoice. In this example I demonstrate how to use the INDEX() Function to lookup the “Unit Price” for each product selected from the Combo Box list on the Invoice. As a best practice, I recommend that you use “Named Ranges” for the “array” that you Index. The INDEX() Function has three arguments:

  1. The ARRAY to Index – In this case our “named range” with three fields (Product Name, Unit Price, Cell Link)
  2. The ROW reference – In this case the cell in the ARRAY that contains our CELL LINK for the Combo Box
  3. Optionally, the COLUMN reference – in this case “2” for the 2nd Column in the ARRAY (Unit Price)

Let me know if my videos in this series have helped you to understand how to use Form Controls in Excel. It took me some time to figure out how they worked; I hope that I can save you some time and ease your frustration in apply them to your forms. Add your comments below or send me an email – danny@thecompanyrocks.com

Find Additional Videos for Form Controls in Excel

Form Controls include Option Buttons, Spinners, List Boxes and more. Here is a link to the other videos in my series on Form Controls in Excel.

Watch Tutorial on YouTube

If you prefer, follow this link so that you can watch this video on my YouTube Channel – DannyRocksExcels

Resources Available at My Online Shopping Site

I invite you to visit my new, secure online shopping site  where you can Learn how you to “Master Excel in Minutes – Not Months!

How to Add a Combo Box Control to an Invoice Form in Excel [ 8:01 ] Play Now | Play in Popup | Download (1645)
Link to this post!
Filed Under: Excel Forms, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , , ,
October 27, 2010 Leave a Comment

How to Lookup a Value Using Multiple Columns in Excel

There are times when the VLOOKUP Function will not return the value that you want to lookup. Suppose, for example, that you need to lookup the “Title” for an employee where the First Name and Last Name are in separate columns. A VLOOKUP() will not help you in this example. Let me show you how to accomplish this.

Key Concepts Covered

  • Use the “&” (Ampersand) to Join two cells that contain Text Values
  • Use the Index() and Match() Functions for the Lookup
  • Create an Array Formula to perform the Lookup – Use “Ctrl+Shift+Enter” with Array Formulas
  • Create and use “Named Cell Ranges” in your formulas to make it easier to enter and understand the formula

Be sure to check out all of the other Excel Video Lessons on Lookups. Click here to go to the Archive of Lookup Function Videos.

I invite you to subscribe to my “Danny Rocks Tips and Timesavers”Video Podcast on iTunes – it is free!

You can learn how to “Master Excel in Minutes!”

How to Lookup a Value Using Multiple Columns [ 6:00 ] Play Now | Play in Popup | Download (1001)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , ,
October 25, 2010 Leave a Comment

How to Use the VLOOKUP Function in Excel

The VLOOKUP Function is one of the essential Excel functions that you need to understand in order to work with data tables, invoices and product listings. In response to numerous email requests, I created this video lesson to walk you through each step.

I share my best practices which include:

  • Use a “named cell range” for your “array.” The “array” is the table that contains the information that you are looking up.
  • Make sure that the “Leftmost” column in your array is sorted in ascending order and that this column contains the values that you are looking for.
  • Use “FALSE” as the optional fourth argument when you need to find an “exact” match in the Leftmost Column.

Work with Excel Workbook Used in This Lesson

Purchase DVD-ROM

You can learn more about essential Excel Functions on my DVD, “The 50 Best Tips for Excel 2007.” You can buy it now!

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

How to Use the VLOOKUP Function in Excel [ 5:36 ] Play Now | Play in Popup | Download (1006)
Link to this post!
Filed Under: Excel Functions, Excel Training, Interactive Webpages, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , ,
September 21, 2010 Leave a Comment

How to Use Excel Lookup Functions to Create a Customer Invoice

Excel has many powerful Lookup Functions. You can use them, for example,  to lookup an Item Number when you know the Product Description. Or to automatically look up the List Price for an Item Number on your customer invoice. In this lesson I show you how to use three Excel Lookup Functions:

  1. The INDEX() Function
  2. The MATCH() Function
  3. The VLOOKUP() Function

I hope that you enjoy learning – and applying – these techniques. I would like to hear from you, so you can either add a comment below or send me an email.

You can receive my new Tips and Timesavers Videos automatically by subscribing to my RSS Feed or to my free podcast on iTunes. Click here to view, download or subscribe to my podcast.

You can purchase any of my 5 DVDs in “The 50 Best Series …” for Excel 2007, PowerPoint 2007, Word 2007, Outlook 2007 and Access 2007 – Click here to open your shopping cart!

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

How to Use Excel Lookup Functions to Create a Customer Invoice [ 5:24 ] Play Now | Play in Popup | Download (789)
Link to this post!
Filed Under: Excel Functions, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers, Uncategorized Tagged: , , , , , , , ,
August 20, 2010 8 Comments

Excel Training Videos by Category

Here is a listing, by category – with hyperlinks – to the free Excel Training Videos that I offer on The Company Rocks website. Simply click on the hyperlink for each Category to go directly to the Archive of Excel videos in that category. 

Note: Some of my Excel Videos are listed in multiple categories.

Excel Tip List

Excel Tips

The companion page to this one is, “Index of Excel Video Training Lessons.” In the Index you will see the title of each Excel Video Tutorial listed beneath the category.

If you like the free videos that I offer, I guarantee that you will enjoy my DVD – “The 50 Best Tips for Excel 2007” – now on sale here on my website.

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

Learn how to quickly “Create Pivot Tables and Charts.”

Excel Training Video Lessons – by Category:

“The 50 Best Tips for Excel 2007” DVD is now on sale!

Link to this post!
Filed Under: Excel Training, Video Training Courses Tagged: , , , , , , , , , , , ,
May 29, 2010 2 Comments

Five Benefits from Using Tables in Excel 2007

I really enjoy the major changes that Excel 2007 has incorporated into Tables. In this video I demonstrate Five Benefits when you convert your Excel data lists into Tables.

(Note: This is a re-post. I now make many of my videos available as Podcasts and I need to send these videos to the iTunes store.)

Click here if you would like to subscribe to my Podcast at the iTunes store.

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

Five Benefits from Using Tables in Excel 2007 Play Now | Play in Popup | Download (1181)
Link to this post!
Filed Under: Excel Data Tables and Lists, Excel Training, New in Excel 2007, Podcast Tips & Timesavers, Tips and Time Savers Tagged: , , , , ,
October 30, 2009 Leave a Comment

Calculate the Days Worked on a Project

Do you manage Projects? Do you create Project work schedules? Do you need to know how many days were actually worked on your Project?

If so, this Excel video lesson is for you. A viewer asked for my help. He did not want to use MS Project for his work schedules. Rather, he wants to know which Excel Functions to use for his Project work schedules.

Excel Functions Covered in this Tutorial

In this lesson, I demonstrate how to use two functions included in the Excel “Analysis Tool-Pak”:

  • =WORKDAY()
  • =NETWORKDAYS()

Index of Excel Video Tutorials

Search for a specific Excel Video Lesson on this website in my Index.

Watch Excel Tutorial on YouTube

Click on this link to watch this Excel Tutorial on my YouTube Channel – DannyRocksExcels

Secure Online Shopping for My Resources

Want to learn how to get the most out of Excel 2007?

My DVD, “The 50 Best Tips, Tricks & Techniques for Excel 2007” is available for purchase.

 

 

Link to this post!
Filed Under: Excel Date and Time Functions, Excel Financial Tools, Excel Functions, Excel Training, Video Response to Viewers Tagged: , , , , , , ,
« Previous Page
Next Page »