Data Validation - Lists from Other Sheets / Workbooks

Alright!! We saw the basics and examples of using data validation in earlier posts. Now let’s see more practical examples of it.

Create a List Validation with reference from other sheet in same workbook:

Many a times, I enter the list items in same sheet but in a faraway hidden column. That is an easy way but few other times, it may not be possible to do so or you want to keep the source list some other sheet so that users cannot mess with it or there is a list available in other sheet and you simply cannot move it to the sheet where validation is required. Unless you are using Excel 2010, Excel will not allow you to take validation reference from other sheets.  Read on to see how we can work in this situation.

Below workaround applies to Excel 2007 and earlier versions. In Excel 2010 or later, you can take reference of source list from other sheets as you do in the same sheet.

We do this in 3 simple steps. Follow closely.

In Excel 2007

1. Create the Source List: Go to the sheet where your list items are entered (enter now if they are not already entered) and select all list items.
2. Create a Named Reference to the source list: Under Formulas ribbon, click Define Name. In the New Name dialogue box, give a name of your choice. But make sure the name does not begin with anything other than a letter or an underscore. Name should not contain spaces etc. For example: I give the name as MyList
3. Create Validation: Select cell(s) you want to use Data Validation and go to Data ribbon > Data Validation. In Data Validation dialogue box, under Settings Tab, select List in Allow: field. Under the Source, type = (equal sign) followed by the name you gave to your source list. In our example, I type =MyList

In Excel 2003 or earlier

1. Create the Source List: Go to the sheet where your list items are entered (enter now if they are not already entered) and select all list items.
2. Create a Named Reference to the source list: Under Insert menu, click Name and then Define. In the Define Name dialogue box, give a name of your choice. But make sure the name does not begin with anything other than a letter or an underscore. Name should not contain spaces etc. For example: I give the name as MyList
3. Create Validation: Select cell (s) you want to use Data Validation and go to Data menu > Data Validation. In Data Validation dialogue box, under Settings Tab, select List in Allow: field. Under the Source, type = (equal sign) followed by the name you gave to your source list. In our example, I type =MyList

That’s it. Check in the cell(s) and ensure you got all the list items appearing in the drop down.

Create a List Validation with reference from different workbook:

For this data validation to work, the workbook that contains the list must be open, in the same instance of Excel. Users will have to open workbook with the drop down lists, and workbook with the original source list.

Let’s think of an example. You have a workbook named List (with extension .xlsx in Excel 2007 or later and .xls in Excel 2003 or earlier) which contains a sheet with your list items. You also have another workbook named Validation (with extension .xlsx in Excel 2007 or later and .xls in Excel 2003 or earlier) and you want to apply validation in this book. Let’s see how we can do this.

1. Create a Named Reference to the Source List: Name the list items in workbook List with a Named Range called MyList. (Refer above example on how to name a range)
2. Open the workbook Validation (you can also create a new workbook). Open the Name Manager dialogue box by clicking on Formulas ribbon and click Define Name in Excel 2007 or later versions (In Excel 2003 or earlier, click Insert menu, click Name and then Define). In the New Name dialogue box (Define Name in Excel 2003 or earlier), type a name of your choice but something different from what you created in workbook List. For this example, I use a name MyListNew. Select Workbook under Scope. In Refers to: type the following carefully. Type = (equal sign), then the source workbook name and extension followed by ! (Exclamation mark), followed by the range name in the source workbook. In our example, I type =List.xlsx!MyList (In Excel 2007 or later) and =List.xls!MyList (In Excel 2003 or earlier).
3. Note: Type an apostrophe (') at the start and end of the referenced workbook name, if it contains a space character. For example: ='List 2014.xlsx'!MyList (In Excel 2007 or later) and ='List 2014.xls'!MyList (In Excel 2003 or earlier). Click OK
4. Create Validation: Select the cells in which data validation will be set. On the Ribbon, click the Data tab, then click Data Validation. In the Allow box, choose List. In the Source box, type an equal sign, then the list name created second time i.e. in Validation workbook, example: =MyListNew and click OK.

That’s it. Check in the cell(s) and ensure you got all the list items are appearing in the drop down. But remember, in order to use this validation drop down lists, both workbooks must be open.

I hope this helps you in work effectively with Data Validation. See you in another post!!

Data Validation - Points to Remember

Hi Guys!! Nice to see you again.

We have discussed Data Validation concept and few examples on how to set up the validation criteria in our earlier posts. Now lets continue with few points we have to keep in mind while using this feature.

1. While creating a list, you can refer the list items from a range with in the same sheet. If your list is in a different sheet, you cannot select that range using the Data Validation window. In such case, you can use the Define Name command (Formulas tab, Defined Names group) to define a name for the range that contains the list. Then type the name in Data Validation window.
2. Input Message will be shown till you deselect the cell. If users do not want it to be there while cell is selected, they can just press Esc. 
3. You can change the place where ‘Input Message’ appears. Once the message is displayed, click on it and drag with your mouse to any place on the sheet. Next time onwards that message will be displayed in its new place. 
4. You can apply data validation to cells that already have data entered in them. However, Excel does not automatically notify you that the existing cells contain invalid data. In this scenario, you can highlight invalid data by instructing Excel to circle it on the worksheet. Once you have identified the invalid data, you can hide the circles again. If you correct an invalid entry, the circle disappears automatically. Click on 'Circle Invalid Data' under 'Data Validation' in the menu to access this feature.
5. 
6. To quickly remove data validation for a cell, select it, and then open the Data Validation dialog box (Data tab, Data Tools group). On the Settings tab, click Clear All. 
7. To find the cells on the worksheet that has data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. Same can be achieved by pressing F5 ('Go To' dialogue box) > Special > Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings.
8. It is not possible to change the font or font size for items in a list. 
9. The width of the drop-down list is determined by the width of the cell that has the data validation. You might need to adjust the width of that cell to prevent truncating the width of valid entries that are wider than the width of the drop-down list. 
10. While applying data validation, you can press F3 while placing cursor in Source field to show all the available Named Ranges. This will be handy to see names available to use in the workbook.

Hope we are all clear on Data Validation. Let’s continue discussion about Data Validation in our next post by discussing few techniques using it. Happy learning!!

Data Validation - Examples

Hello guys!!

We saw technical details about Data Validation in our earlier post. Now, let's continue with few examples where you can use this feature.

1)       Select one or more cells to validate

2)       On the Data tab, in the Data Tools group, click Data Validation

3)       In the Data Validation dialog box, click the Settings tab

4)       Select the appropriate option from ‘Allow’ drop down

Example 1: Restrict data entry to values in a drop-down list

                     i.            In the Allow box, select List

                    ii.            Click the Source box and then type the list values separated by the Microsoft Windows list separator character (commas by default)

For example:

·        To limit entry to a question, such as "Do you have children?” to two choices, type Yes, No

·        To limit a vendor's quality reputation to three ratings, type Low, Average, and High

You can also create the list entries by referring to a range of cells elsewhere in the workbook

                  iii.            Make sure that the In-cell dropdown check box is selected. Otherwise, you won't be able to see the drop-down arrow next to the cell

Example 2: Restrict data entry to a whole number within limits

         i.            In the Allow box, select Whole number

        ii.            In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between

      iii.            Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value

For example, to set a minimum limit of deductions to two times the number of children in cell F1, select ‘greater than or equal to’ in the Data box and enter the formula, =2*F1, in the Minimum box.

Example 3: Restrict data entry to a decimal number within limits

         i.            In the Allow box, select Decimal.

        ii.            In the Data box, select the type of restriction that you want. For example, to set upper and lower limits, select between.

      iii.            Enter the minimum, maximum, or specific value to allow. You can also enter a formula that returns a number value.

For example, to set a maximum limit for commissions and bonuses of 6% of a salesperson's salary in cell E1, select ‘less than or equal to’ in the Data box and enter the formula, =E1*6%, in the Maximum box.

Note: To let a user enter percentages, for example 20%, select Decimal in the Allow box, select the type of restriction that you want in the Data box, enter the minimum, maximum, or specific value as a decimal, for example .2, and then display the data validation cell as a percentage by selecting the cell and clicking Percent Style in the Number group on the Home tab.

Example 4: Restrict data entry to a date within a time frame

         i.            In the Allow box, select Date.

        ii.            In the Data box, select the type of restriction that you want. For example, to allow dates after a certain day, select greater than.

      iii.            Enter the start, end, or specific date to allow. You can also enter a formula that returns a date.

For example, to set a time frame between today's date and 3 days from today's date, select between in the Data box, enter =TODAY() in the Minimum box, and enter =TODAY()+3 in the Maximum box.

Example 5: Restrict data entry to a time within a time frame

         i.            In the Allow box, select Time.

        ii.            In the Data box, select the type of restriction that you want. For example, to allow times before a certain time of day, select ‘less than’.

      iii.            Enter the start, end, or specific time to allow. You can also enter a formula that returns a time.

For example, to set a time frame for serving breakfast between the time when the restaurant opens (the value in cell H1) and five hours after that, select between in the Data box, enter =H1 in the Start time box, and then enter =H1+"5:00" in the End time box.

Example 6: Restrict data entry to text of a specified length

         i.            In the Allow box, select Text Length.

        ii.            In the Data box, select the type of restriction that you want. For example, to allow up to a certain number of characters, select less than or equal to.

      iii.            Enter the minimum, maximum, or specific length for the text. You can also enter a formula that returns a number value.

For example, to set the specific length for a full name field (C1) to be the current length of a first name field (A1) and a last name field (B1) plus 10, select less than or equal to in the Data box and enter =SUM(LEN(A1),LEN(B1),10) in the Maximum box.

Example 7: Calculate what is allowed based on the content of another cell

         i.            In the Allow box, select the type of data that you want.

        ii.            In the Data box, select the type of restriction that you want.

      iii.            In the box or boxes below the Data box, click the cell that you want to use to specify what is allowed.

For example, to allow entries for an account only if the result won't go over the budget in cell E4, select Decimal for Allow, select less than or equal to for Data, and in the Maximum box, enter =E4.

Example 8: Use a formula to calculate what is allowed

         i.            In the Allow box, select Custom.

        ii.            In the Formula box, enter a formula that calculates a logical value (TRUE for valid or FALSE for invalid entries). For example: To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

Note: If your allowed values are based on a cell range with a defined name, and there is a blank cell anywhere in the range, setting the Ignore blank check box allows any values to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, setting the Ignore blank check box allows any values to be entered in the validated cell.

5)       To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.

6)       Optionally, display an input message when the cell is clicked.

         i.            Click the Input Message tab.

        ii.            Make sure the Show input message when cell is selected check box is selected.

      iii.            Fill in the title and text for the message.

7)       Specify how you want Microsoft Office Excel to respond when invalid data is entered.

         i.            Click the Error Alert tab, and make sure that the Show error alert after invalid data is entered check box is selected.

Note:  If you want to allow users to type entries that are not in the list, clear the Show error alert after invalid data is entered check box instead.

        ii.            Select one of the following options for the Style box:

·        To display an information message that does not prevent entry of invalid data, select Information.

·        To display a warning message that does not prevent entry of invalid data, select Warning.

·        To prevent entry of invalid data, select Stop.

      iii.            Fill in the title and text for the message (up to 225 characters).

Note: If you don't enter a title or text, the title uses the default string "Microsoft Excel" and the message is (by default): "The value you entered is not valid. A user has restricted values that can be entered into this cell."

8)       Test the data validation to make sure that it is working correctly.

Try entering both valid and invalid data in the cells to make sure that your settings are working as you intended and your messages are appearing when you expect.

That's enough examples to get you going. Try it out for yourself. In the next post, we will see few points worth noting while using Data Validation. Bye until then!!

Data Validation - The Concept

Hello All!! Today we will be discussing about one more very useful feature of Excel which is Data Validation. I am planning to cover this topic in series of posts (around 5). I planned to write posts starting from the technical side of this concept slowly moving to practical usage side (from boring side to more interesting side, if you put it that way). I am sure most of you are already using this awesome feature but you may find couple of points useful in forthcoming posts. So let’s rock.

Let's assume a situation where in you need to gather certain data from your counterparts across the globe. You created a data template in Excel to send to them. You want;

• to restrict the users to enter anything they please, but allow them to select in few possible options you have
• all of the users to use a standard wording so that consolidation and reporting would be easier for you 
• to provide instructions on how to fill the data 
• to alert them if they are not following what you intended.

You can add an instruction sheet containing all the details required and inform them over a call or mail that they have to read the instructions first and fill the data accordingly. But it is tedious and you cannot even guarantee that your users will follow that. There is this excellent feature of Excel to your rescue i.e. Data Validation.

What is it? 

Data Validation is a method in Excel to control type of data or the values that users enter into a cell. For example you can restrict the users so that they enter only whole numbers or you may want to limit user choices by a list etc. Basically, you can perform following 3 functions using this option.

• Control what user can enter
• Show instructions while the cell is selected 
• Warn or Stop if un-intended data is entered

How to access?

If you are using Excel 2007 or higher, you can access data validation by following; Data Ribbon > Data Tools Group > Data Validation. Short cut key to access this function is Alt+a+v+v. As a compatibility option, Excel supports the previous versions key combination which is Alt+d+l.

Other version of Excel contain this function in Data Tab > Validation. Shortcut key combination is Alt+d+l.

What options are available?

If you are sharing the file for collection of information and want users to fill a consistent and accurate data, Data Validation is your friend. Let’s see in detail what all Excel supports through Data Validation.

When you click on Data Validation, the following screen appears.

You can set the validation criteria in the Settings Tab above. Select appropriate option under “Allow:” field.

If the check box ‘Ignore Blank’ is checked (by default it is), user can edit the cells and can press enter with nothing entered in the cell, it can be blank. Excel will not complain even if ‘blank’ is not permitted as per validation set. If this check box is not checked, a blank entry is also validated and results into error.

Note: If you are selecting 'Custom', you can enter a formula under the 'Formula' field. Excel evaluates the result of this formula with the value entered in the validation cell, if the formula result evaluates to 'True' then Excel accepts the value entered by user, if the result is 'False' then validation fails and Excel throws up the Data Validation messages set below.

Data Validation Messages

Alright, you have set up the validation criteria. But users do not know what values the validation allows and what it doesn't. You can provide the required fill up instruction in the ‘Input Message’ tab in Data Validation dialogue box so that when the cell is selected, instruction is displayed. See the picture below. 

I gave a title and message for validation in Cell A1. When user selects cell A1, this is how it appears.

So that users will have clear instruction on what to enter.

But what if they still do not follow it and enter something which was not intended? You have another tab in Data Validation window i.e. ‘Error Alert’.

I set up validation in cell A1 to allow values 1 to 10 and set up an error message if user enters any value outside that range. See what happens if I enter 11.

My error message is displayed to user. Entered value will not be accepted and user can retry. This is when I select ‘Stop’ under ‘Style’ in ‘Error Alert’ tab. There are 2 more options available under Style field. Look below for explanation for those.

Seemingly fit but Data Validation suffers from a rather serious flaw. It only works if user directly enters value in to the cells. Validation does not work if the cell gets value from a formula, or user copy and paste the values from somewhere else or a macro places the invalid values in the cells.

Help in Excel is fantastically created to explain about Data Validations along with examples. Press F1 and check it for yourself. In the next post, we shall see few examples on how to use Data Validation in real life. See you there!!