Data Validation: Part 3 of 3

November 2, 2011 by Leave a comment

Make your spreadsheets stand out with Data Validation and Dependent Drop-Down Lists

In part one of this Data Validation series, I showed you how simple it is to add validation to your spreadsheets, increasing the ease and accuracy of data entry. In that post, I showed two ways of limiting the allowed values for any particular cell. The most straightforward method involves entering the values directly into the source box. Alternatively, you can enter the allowed values in a column somewhere else on the spreadsheet and then refer to that range of cells in the validation window. The downside to this method is that you must list the values on the same spreadsheet as the field on which you are applying validation, adding unnecessary clutter. Of course, as I mentioned, you could hide the column or put the values well off of the visible screen (e.g. column ZZ or something like that), but you’d have to unhide the column or scroll over to the far right of the spreadsheet every time you wanted to update the list. That is obviously not ideal.

A better way to accomplish this was discussed in part two of this Data Validation Series, where I showed how using a named range allows for a cleaner worksheet. I showed how listing the values you want to allow for a specific cell (field) on another sheet, then naming this range so that you can refer to it in the Data Validation window is cleaner and more efficient than listing them in the Data Validation window itself or separately in a list, but on the same sheet. Read more of this post

Advertisement

Filed under Application (Excel, Access) and Process Development Tagged with , , , , ,

Data Validation: Part 2a of 3

October 17, 2011 by 1 Comment

Convert your named Range into a Table for more flexibility

In part 2 of this Data Validation Series, I showed how using a named range when adding data validation to your fields allows for a cleaner worksheet. Listing the values you want to allow for a specific cell (field) on another sheet, then naming this range so that you can refer to it in the Data Validation window, I showed you, is more efficient and cleaner than listing them in the Data Validation window itself or separately in a list, but on the same sheet.

But we can enhance this method even further. Using the named range, you might have discovered, makes adding an item to the list difficult. You either have to change the referenced range area each time you add an item or you have to make the original range large enough to include further inclusions. Unfortunately, creating a range with blank rows leaves spaces in your data validation list.  That’s obviously not ideal or neat.

A great workaround for this is to convert the range of allowable values into a table as follows: Read more of this post

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , , ,

Data Validation: Part 2 of 3

October 3, 2011 by 2 Comments

Using named ranges is a very useful trick and can be applied in a lot of ways, including data validation.

In part one of this Data Validation series, I showed you how simple it is to add validation to your spreadsheets, increasing the ease and accuracy of data entry. In that post, I showed two ways of limiting the allowed values for any particular cell. The most straightforward method involves entering the values directly into the source box. Alternatively, you can enter the allowed values in a column somewhere else on the spreadsheet and then refer to that range of cells in the validation window. The downside to this method is that you must list the values on the same spreadsheet as the field on which you are applying validation, adding unnecessary clutter. Of course you could hide the column or put the values well off of the visible screen (e.g. column ZZ or something like that), but you’d have to unhide the column or scroll over to the far right of the spreadsheet every time you wanted to update the list. That is obviously not ideal.

Indirect Function: using named range as the source.

You can simplify things by applying a few simple techniques. While the “Source” box on the data validation window doesn’t allow a range outside of the current worksheet, it does allow named ranges, which can refer to these external ranges. To do so, however, requires the use of the Indirect Function, which will treat anything you feed it as if you’d typed it directly. While typing “animals” into the “Source” box will create a drop down list of one value- “animals”, for example, using Indirect(“animals”) will create a drop down box with the values of the named range called “animals.”

Specifically, apply the following steps to use this method:

  1. On a separate worksheet (which you can later hide if you wish), enter the values you wish to allow
  2. Name the range by using one of the following methods:
    1. Under Formulas Ribbon, choose “Name Manager”; then create “new”
    2. Select the range containing the values; in the address bar, type the desired name; in our example, we use “animals”
  3. On the “Data Tools” Ribbon, select Data Validation/Data Validation; the “Data Validation” window will appear
  4. On the “Validation Criteria” tab, select “List” under for the “Allow” option
  5. The “Source” box will now be active; in type:

    =INDIRECT(“animals”)

  6. Hit the “Ok” button

It’s that simple. In part three of this series, I’ll show you another cool trick. What if you wanted to restrict allowable values in a cell based on the value in another field? For example, let’s say you have a field for “State.” Depending upon the value in that cell, you then want to allow a finite list of cities to be selected. Again, with the use of named ranges and the Indirect function, this is a lot easier than you might think.

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , ,

Data Validation: Part 1 of 3

September 30, 2011 by 2 Comments

Get familiar with the Data Validation Ribbon; it can save you from headaches due to errors in data entry.

If you work with data often, and especially if you collaborate or share your information and work with others, you know this to be fact: like my kids when they tell me they are going to sleep when I know they’re not; if left unsupervised, your colleagues will undoubtedly screw up your spreadsheet. Of course that is partly poking fun, but you get the point and can certainly relate. One of the biggest problems I’ve confronted over the years working with data has to be maintaining accurate and easy data entry.

Fortunately, the two–accuracy and simplicity– go hand in hand. Making the process simple by reducing the potential for human error is paramount. This cannot be understated and serves two purposes. As mentioned in one of my favorite books, The E-Myth Revisited by Michael E. Gerber, one of the biggest mistakes people in business make is making the success of their company, department, or group too heavily dependent on smart people and excellent workers. While I certainly understand the importance of a good team, something that revealed itself time and time again while playing sports my entire life, I also know that having a great structure and process that allows players or employees of different types and skills to succeed, is as important and effective. Making the structure of the “program” simple and effective is step one. Once that is accomplished, communicating that structure clearly and explicitly so that those charged with carrying out the plan will be successful is step two and a natural extension of step one.

The Data Validation Window allows you to customize data entry, user communication, and error handling.

In the working world, this theory manifests itself in many ways. One small way is in the countless reports, processes, and other forms of communications we all share between and amongst each other. Within that context, Data Validation in Microsoft Excel can be an extremely helpful tool. Instead of allowing free and manual entry, I am of the mindset that whenever possible you should restrict the possibilities people have and, therefore, probability of error. Not only does this ensure accurate information is collected and shared. But it also allows employees to perform their jobs well, thus enjoying them more, and frees them up to perform more important duties and grow professionally in business-critical areas.

It’s really pretty simple. To make sure data is entered correctly, apply data validation to a cell, by doing the following:

  1. Select the cell on which you’d like to apply validation
  2. On the “Data Tools” Ribbon, select Data Validation/Data Validation; the “Data Validation” window will appear
  3. There are three tabs on the “Data Validation” pop-up
    1. Settings: Under ‘Validation Criteria’, select “List” for the “Allow:” option
      1. The “Source:” box will then appear. You can either type in the options, separated by a comma; or enter a range of cells that contains the list of values you allow.
    2. Input Message: Allows you to enter text that appears when the cell with validation is selected
    3. Error Message: Allows you to customize the message that appears if invalid data is entered

  4. Using a range on your spreadsheet to limit values is great, but hiding them from the user is even better. I'll show you how in Part II.

    Select OK

Very simple, right? Well, what if you want to use a range for the list, but don’t want it to appear on the same sheet, cluttering up things. In part two of this series, I’ll show you a trick for that.

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , ,

Getting Unique Values Only

September 7, 2011 by Leave a comment

Often times when you are working with Excel, you want to reduce a list of items so that only the unique instances are listed. In other words, if, within a column or list an item happens to be duplicated, you want that item to appear only once. Maybe you have a mailing list, for example, and fear that some customers may be entered more than once. Or perhaps you’ve been collecting emails and are now ready to send out promotional or “Thank you” emails, but don’t want to spam people with multiple messages.

Luckily, Excel makes it very easy to do this, saving you from yourself. In fact, there are two ways. One is simpler and built in to Excel’s multiple functionalities. The other is a bit more difficult and less intuitive, but is more flexible and dynamic. I explain them both to give you flexibility in dealing with these types of business problems. For my example, I use a simple list of animals where a couple of them (cat, bird) are each repeated.

Note: This tip is for Excel 2007, but is applicable, with a tweak or two, with other versions of Excel.

Method 1:

  1. Select data from which you want to retrieve only the unique values. Underline, or otherwise differentiate, the header so that Excel can recognize it as such.
  2. Under the “Data” Ribbon, select the “Advanced” Option.
  3. Under Action, select “Copy to another location.”
  4. The “List Range” should be populated with the data set selected in Step 1
  5. Choose the same range for the “Criteria Range” (I normally copy and paste) from above (the “List Range”)
  6. Check the box next to “Unique Records”
  7. Hit the “OK” button Read more of this post

Filed under Application (Excel, Access) and Process Development, Business Stuff Tagged with , , , , , ,

← Older posts