Showing posts with label Data Validation. Show all posts
Showing posts with label Data Validation. Show all posts

Friday, February 21, 2014

Data Validation - Cascading Lists (Using INDEX & OFFSET function)


Hello. We already saw 3 ways of getting cascading lists in your workbook using IF function, INDIRECT function & CHOOSE function. Let’s also see one more way of doing this using combination of INDEX and OFFSET functions. This is one of my preferred way, reason being you end up using very few Named Ranges. One more benefit is you can keep on adding new items in the list without worrying about modifying formula’s or anything. Let’s dive deep and find out how.

For a quick recap of our example, we have country, region and state to be selected from the drop down lists and we wanted these lists to be cascading / dependent on previous option selected.


For a change, I kept my database in a different sheet. Let’s name this sheet as Data Base. Also name the sheet where you are applying validation as Data Entry. Going forward, we are going to use these names frequently so remember them.

Arrange Your Data

To get this method to work, you will have to arrange your data in a particular way. Look at below picture on how I arranged it.


In column A of Data Base sheet, enter items you want in first drop-down list with a heading in cell A1. In our example, we have 2 countries to be given.

Moving to the right of column A, make one column for each item of the first drop down list with item name in first cell of the column. In our example, I entered India in cell B1 and USA in cell C1.

In each column, enter the drop-down list items relating to that heading. In above example, I enter South India, North and so on under India. Enter the items for USA as well.

Moving to the right, for each item in the second drop-down, create a different column by following the logic in previous paragraph. Once you are done arranging your data, your Data Base sheet would look like as in above picture.

Define Named Ranges

As mentioned above, we will be using very few Named Ranges. In fact, we are going to use only 3 of them. Go to your Data Entry sheet and do the following.

Named Range 1:

Click on Formulas ribbon > Define Name. In the New Name dialogue box, give a name FirstList. In the Refers to: field, enter the below formula
=OFFSET('Data Base'!$A$2,,,COUNTA('Data Entry'!$A:$A)-1)
Click OK. This formula creates the first drop-down lists with countries in our example. This formula is dynamic, that means it's size increases as you type new entries.

Named Range 2

Click on Formulas ribbon > Define Name. In the New Name dialogue box, give a name DataRange. In the Refers to: field, enter the below formula
=OFFSET('Data Base'!$A$2,,,100,COUNTA('Data Entry'!$1:$1))
Click OK. This formula creates an array of cells with possible drop-down options which we will use in the next Named Range. This is also dynamic range.

Named Range 3

Select cell B2 and click on Formulas ribbon > Define Name. In the New Name dialogue box, give a name DropDownList. In the Refers to: field, enter the below formula. (This formula is long; it should be in one line)
=OFFSET(INDEX(DataRange,1,MATCH('Data Entry'!B1,'Data Base'!$1:$1,0)),,,COUNTA(INDEX(DataRange,,MATCH('Data Entry'!B1,'Data Base'!$1:$1,0))))
If you notice, this formula uses Relative References. Selecting cell B2 before entering this formula is absolutely necessary for this formula to work as expected.

This formula checks for the option selected in cell B1 (first drop-down) and finds exact value in Data Base sheet and returns all the items under that column.

Once you are done naming as above, your Name Manager should look as in picture below.



Apply Validation

Last step is to apply validation.

Select cell B1 and select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type  =FirstList

Select cell B2 and select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type  =DropDownList

Select cell B3 and select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type  =DropDownList



That’s all. Check the drop-down lists and its list items.

You will just have to expand the Data Base sheet if you have additional entries in the future. For example, if you got one more country to be added, go to Data Base sheet and add the new country name in cell A4. In last column to the right, enter the new country name in first row along with options under it in below cells. Follow the logic in Arrange Your Data section above. That’s all you have to do. Validation lists will be updated automatically. Once you are done with arranging, go to validation lists and check it.

You can download the example file I used by clicking here. Post a comment if you have any doubts relating to this post.

I am concluding Data Validation series with this post. I will only make another post on this feature when I feel there is some useful information I should add. Meanwhile, let me know if you want me to cover any particular scenario. Happy Learning!!

Data Validation - Cascading Lists (Using CHOOSE function)


Hola! Bienvenido! That’s hello and welcome in Spanish (J)

Coming to the point, we have seen how to build cascading lists using IF function and INDIRECT function. There are couple of more ways you can do the same, so I thought to put it here. Let’s see one of those ways in this post. I will set the basic backdrop using our example in earlier posts.

We have country, region and state to be selected from the drop down lists and we wanted these lists to be cascading / dependent on previous option selected.


In the same sheet, from A6 and onwards I have the countries, regions and states list. Let’s arrange them as in the table shown below. We are going to give numbers for each country and region list. For example: For India, I gave number 1 and USA got 2. Same way I gave numbers to regions list. You need not number State List. We will have to number this way until last but one layer. We will see how these numbers will be used going forward. Your data will look like below once done.



Select the first list values and give a name of your choice. In my example above, I select A7:A8 and name it as CountryList (Refer this post on how to give names to ranges)

From the second list, select those options relating to first item in first list and name it. In above example, I will select A12:A15 (regions relating to first item in first list i.e. India) and name it as India.

From the second list, select those options relating to second item in first list and name it. In above example, I will select A16:A19 (regions relating to second item in first list i.e. USA) and name it as USA.

You will continue doing this until all items in second list are named respectively. Once this is done, we will move on to third list items.

From the third list, select those options relating to first item in second list and name it. In above example, I will select D7:D8 (regions relating to first item in second list i.e. South India) and name it as SouthIndia.

From the third list, select those options relating to second item in second list and name it using exact wordings as the second item in second list. In above example, I will select D9:D10 (regions relating to first item in second list i.e. North India) and name it as NorthIndia.

Go on and name all other items following logic above. Once we are done naming all the items in third list, our list of named ranges should look like below. (I didn't name USA related items yet, but you have to in your real life example)



Alright, we are done arranging our data. Let's move on to apply validation in the cells.

In cell B1, apply validation as you normally do. That means, you will select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type =CountryList

In cell B2, you will select List in Data Validation dialogue box Settings tab Allow: field. In the Source field, type the below formula!
=CHOOSE(VLOOKUP($B$1,$A$7:$B$8,2,0),India,USA)
In cell B3, you will select List in Data Validation dialogue box Settings tab Allow: field. In the Source field, type the below formula!

=CHOOSE(VLOOKUP($B$2,$A$12:$B$19,2,0),SouthIndia,NorthIndia,EastIndia,WestIndia)

Go ahead and check the drop-down lists. Are they working like cascading lists?





Let’s see what our functions are doing in cell B2.

VLOOKUP function looks for value of B1 in table A7:B8 and returns a number from 2nd column (that is where we gave numbers). Result of this is 1 if you select India in B1 and 2 if you select USA. CHOOSE function will select named ranges India or USA based on the VLOOKUP result. Named Range India will be selected if VLOOKUP result is 1 and Name USA is selected if VLOOKUP result is 2. Same logic for other lists as well.

You can download workbook I used for this example by clicking here.

There is one more way we can get the cascading lists i.e. using INDEX function. I will write about that in next post. Stay tuned!

Data Validation - Drop-Down List of Unused Items Only


Hello again, I thought of completing discussion on Data Validation with 5 posts but we have 9 posts so far and I still have material to put forward. Anyway, let’s see another practical example on using Data Validation lists.

Material for today’s post is based on a query I received in one of the Excel online forum, I spent some time answering that. Let me summarise the issue and provide a solution.

Primarily I need to have a drop-down of items across cells in such a way that when I select any one item from the list in a cell, drop-down in other cells should not contain that item any more.

Let’s see it by an example. I have 10 parking slots and I need to allot them to vehicles coming in. Slot once allotted to a vehicle cannot be allotted to another vehicle during the same day. As usual, I am maintaining this in versatile Excel. I am maintaining one sheet with the slot names; this sheet acts as my database. I name the sheet as “Database”. In another sheet named “Allotment”, I have 3 columns namely Date (column A), Vehicle Number (column B) & Slot allotted (column C). This is where I maintain daily allotment. Column C here contains the drop-down list of Slots available for allocation.


As mentioned above, I cannot allot a slot already given to some other vehicle. So, to be extra sure I do not want my drop-down containing already allotted slots. Can I do that, if yes, how?

Answer to the above question is YES. Let’s see how. We will do it in 3 steps. Follow closely and side by side, do the same steps in your workbook too.

Step 1: Check if Value is Already Used

Since the drop-down should not contain used items, first let’s find out if any of the items is used in Allotment sheet already. For this, I use COUNTIF function.

Enter the below formula in cell B2 of Database sheet and drag it over till B11.
=IF(COUNTIF(Allotment!$C$2:$C$11,Database!A2)=1,"",ROW())
Once you copy the formula across B11, you will have the sheet like in below picture.


Formula above will check if value in A2 (parking slot number) is already allotted (in Allotment sheet C2 to C11, that’s where we have allotment column). If it is already available there then COUNTIF returns value 1 which IF function uses to return a blank value (denoted by quotes with nothing in-between). If this slot is not used yet in Allotment sheet then COUNTIF returns a value 0 which IF uses to produce the row number of the parking slot we are evaluating.

In Cell B2, since slot FF-1 is not used in Allotment sheet, row number of cell A2 which is 2 is returned by our formula. Same logic until cell B11! To cross checking our formula performance, try entering FF-1 in cell C2 of Allotment sheet and see how these values change in Database sheet. (Check the picture below, once you are done checking, delete the cell C2 entry in your sheet)


Step 2: Determine the Items to Appear in List

Now we know which items are not used yet (denoted by number in column B of Database) and which items are used (denoted by blank entries), let’s make the drop-down list with available names.

Enter the below formula in cell C2 of Database sheet and drag it over till C11. (Long formula, should be in one line)
=IF(ROW(A2)-1>COUNT($B$2:$B$11),"",INDEX($A$1:$A$11,SMALL($B$2:$B$11,ROW(A2)-1)))
Once you are done with it, your database looks like in picture below.


We will analyse this formula in parts.

Part 1: ROW(A2)-1

ROW function returns the row number where we have this formula. We are reducing this number by 1 since we are starting our formula 1 row below starting row (since we have headings in first row).

Result of this formula in our example is 1.

Part 2: COUNT($B$2:$B$11)

COUNT function will see how many cells in B2:B11 are having numbers in them. These numbers are basically the non-used items from our first step.

Result of this formula in our example is 10.

Part 3: IF function

IF function validates if the value returned by ROW function minus 1 is more or less than the number COUNT returned. If the ROW minus 1 is equal to COUNT result (that means we came to end of number of available list items), IF returns a blank entry (denoted by quotes with nothing in-between). If not then last part of the function is evaluated.

Result of IF in our example is FALSE (part 1 result is not more than part 2 result), so IF function evaluates the final part.

INDEX($A$1:$A$11,SMALL($B$2:$B$11,ROW(A2)-1)))

Part 4: ROW(A2) - 1

This part is exactly same as Part 1 above. Result is 1.

Part 5: SMALL function

SMALL function is used to return k-th smallest value in the given range. In our example, SMALL takes 1st smallest (result of part 1) from cells B2:B11 which is 2.

Part 6: INDEX function

INDEX function returns the n-th position value in the array given. In our example, INDEX returns 2nd (result of part 2) cell value from range A1:A11 which is cell A2 value i.e. FF-1.

So on and so forth till cell B11. Now, do check by entering FF-1 in cell C2 of Allotment sheet and see how these values change in Database sheet. This is how it looks like. (After you check, delete what you entered in cell C2)


Step 3: Apply Validation

Don’t fret. This is the last step and we are almost there. Select cells C2:C11 and apply validation. In the Allow: field of Data Validation window, select List. In the Source field, enter the below formula.
=OFFSET(Database!$C$2,,,COUNT(Database!$B$2:$B$11))
This formula creates an array from C2 of Database (where first unused item is listed) till number of non-blank cells in range B2:B11 (refer part 2 of our function analysis above). In this example, since COUNT returned 10, OFFSET function returns list starting from B2 counting 10 cells below which is B2:B11.


You can now see that cell C2, C3 and so on till C11 contains all the items in the drop-down. Now see the magic. Select FF-1 (or any item of your choice) in cell C2 (or any cell of your choice from C2:C11). Go to cell C3 (or any cell other than what you selected in previous line) and check the drop-down. Can you see the item you selected in previous line in the drop-down?



Ta da… it’s done. To get more understanding on how the list is picked up, check the Database tab every time you select an item in drop-down and see how the results of formulas are changing. You can download the workbook I used to present this example by clicking here.

I tried to explain as much as possible but I am sure something might not be well put. Let me know if any part of this post is not clear. Happy learning!!

Wednesday, February 19, 2014

Data Validation - Dropdown List With Symbols

Hi Guys, good to see you again.

We have been seeing about Data Validation and related items over few previous posts. We saw how to apply validation to restrict the users to select only those options you provide as a drop-down  I already said that you cannot change the font that your drop-down list uses. By the way, Excel drop-down lists use the font ‘Tahoma’. This font is normally alphanumeric.

Situation

Recently, I wanted to create a validation in team’s self-appraisal template. There is this field where each team member will have to select what they feel about their performance in the sense is it moved upwards or downwards or no change compared to last quarter. Of course, I can provide the drop-down options using the text. But to be more appealing and creative (J), I wanted to use arrow symbols in the drop-down list showing the directions.

Normally, I use font “Wingdings 3” to get the arrows I want. I type alphabets “p”, “q” and “tu” (without quotes and all in small case) in 3 cells, let’s say in range A1:A3. I then select these 3 cells and change the font to “Wingdings 3”. At this point of time, these 3 cells appear like below.


Though arrow is appearing in the cell, value behind is actually “p” (Notice the formula bar), also see the font above.

Now the next step, I have to create a validation at some other place using these arrows so that a drop-down appears with these options. So let’s do it in cell B1. Remember steps to create drop-down?

Excel 2007 or later

Select cell(s) you want to apply Data Validation and go to Data ribbon > Data Validation. In Data Validation dialogue box, under Settings Tab, select List in Allow: field. Under Source, select range you want. In our example, I select A1:A3.

Excel 2003 or earlier

Select cell(s) you want to apply 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, select range we want. In our example, I select A1:A3.

Check in the cells now. Is it success? Have we successfully created the drop-down using the arrows?

The Problem

Ah. I have the drop-down list but the options are not arrows but alphabets “p”, “q” and “tu”. If I select one the option, the same letter appears in the cell. Wait a minute; this is not what I wanted.

Now, I can format cell B1 to have the font “Wingdings 3” so that when my team selects option “p”, cell actually shows an arrow facing upwards just like below.


Are we successful now? If you say yes, please scroll to top and read situation again. What I wanted was to use arrow symbols in the drop-down list showing the directions. But look above picture, what I have in drop-down are alphabets, not arrows. This alphabets in the drop-down creates confusion among users. So, how to get what I want?

The Solution

We will use something called Special ALT characters to achieve this i.e. working with symbols. Excel supports what is called as Special ALT characters. These are essentially symbols that Excel takes literally but input method to get the symbols is through your keyboard.

Below is the extract of few of the symbols and ALT code for them.


You can download complete list of these codes by clicking here.

If you are working on the same sheet where we have earlier changed font to “Wingdings 3” etc, clear off all the formatting done so that sheet will be in it default condition. Or simply work on a fresh sheet. Follow closely from now on.
  1. Select first cell where you want symbol.
  2. Make sure that the NumLock key has been pressed to activate the numeric key section of the keyboard.
  3. Press the Alt key.
  4. While the Alt key is depressed, type the proper sequence (only the numeric part of the ALT code from the table above) of numbers on the numeric keypad. Note that you should use number key pad on the right side of keyboard and should not use the top row in your keyboard where you have numbers. (If you are using a laptop without numeric keypad, check the keys m, j, k, l, u, i, o, 7, 8, 9. You will find numbers noted with a smaller font below the alphabets / numbers, you will have to press Fn key to activate them. One more option is to enable On-Screen Keyboard through Start menu > All Programs > Accessories > Accessibility > On-Screen Keyboard)
  5. Release the Alt key, and the character will appear.
  6. Repeat the process for all the cells
So to get arrows like how I wanted, I type 30, 31 in cells A1, A2 respectively and 17 & 16 in cell A3. Once this is done, you will be able to see something like in the picture below.


Now notice the font, this will be your default font. Formula bar contains the actual symbol instead of an alphabet. Now go ahead and apply validation in cell B1.


Mission accomplished. Hope you enjoyed this post as much as I did. Happy learning!!

Data Validation - Few Practical Examples

Welcome back. Hope you are doing well in learning Excel. As part of our series on Data Validation, we saw how to use Data Validation with examples in an earlier post. Today, we will see few of such practical examples to sharpen our understanding of this concept even further. 

From now onwards, I assume we are aware of how to access Data Validation from the ribbon or menu so I will not mention it. If you are in doubt, please refer to any of the previous posts on Data Validation.

As we move forward, remember the below points:
  • Unless otherwise mentioned, I try to apply validation in cell A1 in all the below examples.
  • All the formulas are to be entered in Formula field in Data Validation window > Settings tab > Allow: > Under Custom option.
  • Once you enter the formula, check if the validation is working or not

Let’s start.

Allow Only Text Entry

We have an option in the Data Validation to allow only whole numbers or fractions. But there is no option if you want to allow only text entries. For this we have to use custom validation. Enter the below formula in the validation window.
=ISTEXT(A1)
This formula evaluates entry in cell A1, if the result is true (means text is entered) then entry is accepted. Otherwise error message is displayed. You may also provide Input Message & Error Alert that you want Excel to show.

Tip: Try using functions ISEVEN (to restrict to enter only even numbers), ISODD (odd numbers only), ISLOGICAL (True or False only), ISNONTEXT (non-text values only), ISNUMBER (only numbers).

Prevent Total Exceeding a Limit

When you have defined budget amount for example, you would want users to limit the purchases with in the budget. We already know how to restrict number between a minimum and maximum limit, but in only one cell. So, how to limit entries that are exceeding the budget over multiple cells?

We will take an example to understand this easily. I have a budget of 1,000 for my department. I can buy multiple items within this budget. I record my first purchase value in cell A2 and whenever I buy again, I continue to enter the next purchase values in cell A3 and below. I want an error alert when I try to enter the purchase after I exhausted my budget. Enter the following formula.
=SUM($A$2:$A$100)<=1000 
In above formula, I assumed that my purchases won’t go beyond cell A100. I am sure you will change this formula to suit your needs. 

Tip: Mess with COUNT, AVERAGE etc functions too. 

Allow Only Weekdays Entry 

No one wants to work on weekends, unless forced to by their retarded manager. Let’s see how you can restrict entry of weekend dates. Type below formula!
=AND(WEEKDAY(A1)<>1, WEEKDAY(A1)<>7)
This formula uses the WEEKDAY function to see the day of the date entered. WEEKDAY returns 1 for Sunday and 7 for Saturday. We are using these values further to accept or restrict user entry.

Note: You have to make sure that users are not using copy & paste in the cells where validation is applied, if they do, your validation will not work. We also know that values resulted by formulas will override the validation.

Prevent Duplicates

You can use COUNTIF function to prevent duplicates in a given range. Let’s go by an example. I need to apply validation in cells A1:A10 so that no value is entered more than once in these cells. Select A1:A10 cells and in validation, enter below formula to achieve this. 
=COUNTIF($A$1:$A$10,A1)<=1
Tip: Get a formula to allow any one value multiple times and prevent others to be duplicated. Use IF function.

Prevent Entries with a Particular Word

We use Dropdown list if you want to enter user to select a particular word. What if I want user to restrict entering a particular word but allow all others? Let’s assume that word is “Abcd”. You will enter below formula in the cell to restrict this from being entered. 
=A1<>"Abcd" 
This works when only “Abcd” entered in the cell. But this won’t work if user enters this word as part of a sentence like “I am entering Abcd here”. If you want to restrict this way of entries too, then enter below formula. 
=ISERROR(FIND("Abcd",A1))=TRUE 
But FIND is case sensitive. Meaning, “Abcd” is not same as “ABcd” or “ABCD”. Taking our example further, if you want to restrict entry of “Abcd” in any case then enter the below formula. 
=ISERROR(FIND("ABCD",UPPER(A1)))=TRUE 
Tip: Try using SEARCH, SUBSTITUTE functions to achieve the same results. 

Prevent Space & Special Characters 

Sometimes you may want to restrict users from entering spaces or any other special characters like “!”, “@”, “#” etc. Use the below formula in that case. 
=A1=SUBSTITUTE(A1," ","") 
There is a space in highlighted quote marks. Replace that space with any character you want to prevent entry. SUBSTITUTE function replaces space character with “nothing” (denoted by two quotes with nothing in-between). Then this formula checks that entry in A1 is equal to the entry without spaces. 

But this formula won’t work if entry is a number. For this we will use another function of Excel i.e. LEN. LEN calculates the length of a cell means number of characters entered in a cell. This number includes spaces. Enter the below formula if the validation to be performed on number. 
=LEN(A1)=LEN(SUBSTITUTE(A1," ","")) 
Tip: Try to achieve the same result by using ISERROR & FIND function as in one of the example above. 
Above example can be applied for space at any position in the cell entry. 

No Space as First or Last Character 

You might sometimes don’t want a space at the start or at the end of an entry. But a space anywhere else would be fine. In this case, you can use below formula. 
=A1=TRIM(A1) 
Multiple Criteria 

What if I have multiple criteria to be applied for an entry in a cell? For example: I want unique numbers with exactly 3 Digits in cells A1:A10. Look at the below formula to get this validation. 
=AND(COUNTIF($A$1:$A$10,A1)<=1, ISNUMBER(A1),LEN(A1)=3) 
COUNTIF function makes sure that each entry is unique. ISNUMBER prevent text and LEN ensures number of characters is 3.

We can build validation formulas depending on the situations. These practical situations are literally unlimited and it is humanly not possible to guess all and provide ready-made examples for them. But if you follow the basic principles of formulas in validation, I am sure you would be able to come up with a solution. If you are not able to get a solution for your problem, just let me know and I will try to help. Happy Excelling!!

Friday, February 14, 2014

Data Validation - Growing List / Dynamic List

Welcome back. In this post, we will talk about one issue I frequently face while using validation and how to overcome that. I am sure many of you may also face this kind of problem but deal with it in boring way. Let’s dive into the problem and find out a solution.

The problem I am talking about is growing number of list items. We know that we create a Named Range to refer to a range and use the names later in validation. If you remember our example on using validation lists, I took 2 country names viz. India, USA and named it as CountryList which refers to range A7:A8.



What if I realised later that I had to take one more country which I entered recently in cell A9? I will have to open the Name Manager and edit the reference to include that one more cell. If your list is dynamic and change like this very frequently, would you like to do this manual task all the time? Just like any other time, Excel is to your rescue. Let’s see how.

Frequently changed list is what I call a Dynamic List. You can make Excel change the reference automatically each time an entry is added to the existing list. There are 2 ways to do this according to me.

Dynamic Named Range by OFFSET function

Select the list items you want and click on Formulas ribbon Define Name. In the New Name dialogue box that opens up, give the name of your choice (of course, you will have to follow naming conventions). In the Refers to field, enter the below formula.

Excel 2003 or earlier versions
=OFFSET(Cascade!$A$7,0,0,COUNTA(Cascade!$A$7:$A$65536))
Excel 2007 or later versions
=OFFSET(Cascade!$A$7,0,0,COUNTA(Cascade!$A$7:$A$1048576))
In the above formula, I assumed that your list is in a sheet named Cascade, list items started from cell A7. You can modify the formula as per your requirement. This validation drop down list items will be automatically updated as you add more items to your list.

Let’s also quickly see what this formula is doing. COUNTA function calculates how many non-blank cells are there in the range starting from A7 till end row of the sheet (65,536 rows in Excel 2003 or lower & 1,048,576 rows in other versions). OFFSET function creates an array of cells from A7 moving down by number of non-blank cells calculated by COUNTA.

Next, go ahead and use the name in validation.

Points to note:
  1.  There should not be any blank rows in between the list items.
  2. Instead of creating a name and using the name in validation, you can directly enter the formula in the validation source. But as we saw, using names will be less messy in complex situations.

Dynamic Named Range using Tables

If you are using Excel 2007 or later version, you can use this method to achieve dynamic named range. We will be taking help of Excel’s another excellent feature Table here. In our example, we have list of countries in A7:A8 with a heading for this list in A6. Select cells A6:A8 and click on Insert ribbon > Table. Below is what you should see at this point of time.


In the Create Table dialogue box that pops up, make sure A6:A8 is the range mentioned. If not, you can now select the proper range by clicking on that field. Also make sure that the check box “My table has headers” is checked and click OK. Now you can notice the range you selected has changed colours.

Now open the Name Manager. Do you notice there is new member added in the names? By default it will be named as Table1 (or Table2 and so on depending on how many tables you already have in the workbook). If you don’t like the name, you can change it by clicking on Edit in Name Manager.

That’s it. You have created a table which is a dynamic range itself. Notice the range this table refers to. Now go to end of this table (table ends at A8, that’s what we selected for table, right?) i.e. cell A9 and enter any text there. Now open Name Manager and notice the reference. If everything goes good, you should see the reference already changed and new reference will end at cell A9. But remember, you should enter any new items from the very next cell of the end of the table, otherwise your table will not consider the new entry as part of it.

Bingo, you have a dynamic range name which can be used in your validation right away. Go ahead and try it. Let’ me know if you have any doubt or could not get what we are talking about. Catchya later!!

Data Validation - Cascading Lists (using INDIRECT function)


We have discussed on how to use cascading lists in our earlier post. We also saw using IF function will be messy when you have multiple options under drop down menus. I promised that we will look into less messy options in this post, so let's go ahead and see about that.

As the title of this post suggests, this method includes using of INDIRECT function. For this to work, we need to first arrange our data in a structured form. We will be using the Named Ranges extensively. Let's quickly recap our example from earlier post and what we already know about INDIRECT function.

We have country, region and state to be selected from the drop down lists and we wanted these lists to be cascading / dependent on previous option selected. We also know that INDIRECT function will literally evaluate the reference address given as an argument. Okay, let's start arranging our data.


Define Named Ranges for your data

Select the first list values and give a name of your choice. In my example above, I select A7:A8 and name it as CountryList (Refer this post on how to give names to ranges)

From the second list, select those options relating to first item in first list and name it using exact wordings as the first item in first list. In above example, I will select B7:B10 (regions relating to first item in first list i.e. India) and name it as India. Remember, you have to use the exact name.

From the second list, select those options relating to second item in first list and name it using exact wordings as the second item in first list. In above example, I will select B11:B14 (regions relating to second item in first list i.e. USA) and name it as USA. Remember, you have to use the exact name.

You will continue doing this until all items in second list are named respectively. Once this is done, we will move on to third list items.

From the third list, select those options relating to first item in second list and name it using exact wordings as the first item in second list. In above example, I will select C7:C8 (regions relating to first item in first list i.e. South India) and name it as SouthIndia. Remember, you have to use the exact name but since Named Range does not allow a space in it, I will have to remove space and name it. We will see below how this can work. But remember again, apart from space in between, entire name should be exactly same.

From the third list, select those options relating to second item in second list and name it using exact wordings as the second item in second list. In above example, I will select C9:C11 (regions relating to first item in first list i.e. North India) and name it as NorthIndia. Remember, you have to use the exact name but since Named Range does not allow a space in it, I will have to remove space and name it. But remember again, apart from space in between, entire name should be exactly same.

Go on and name all other items following logic above. Once we are done naming all the items in third list, our list of named ranges should look like below. (I didn't name USA related items yet, but you have to in your real life example)


Alright, we are done arranging our data. Let's move on to apply validation in the cells.

Apply Validation

In cell B1, apply validation as you normally do. That means, you will select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type =CountryList

In cell B2, you will select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type =Indirect(B1). In my example above, I do not have spaces for list items in first list. So the names given for second list items are exactly same as first list items. However situation may not be same for all. If you have spaces in first list items (like Republic of India), you would have named second list items as RepublicOfIndia (without spaces). In this case, you will have to type =Indirect(Substitute(B1," ","")). This formula replaces all spaces with nothing so your name and list value will be exactly same. We are going to use the same logic in our third validation. If while doing this, cell B1 value is not selected already, you may get an error saying this formula evaluates into error as in below picture. If your formula is correct, you can safely click Yes.
Error screen if B1 is empty
In cell B3, you will select List in Data Validation dialogue box Settings tab Allow: field. In the Source, type =Indirect(B2). We have spaces in second list items (example: South India), so we have named  third list item as SouthIndia (without spaces). Going by above logic, we will have to type =Indirect(Substitute(B2," ","")). This formula replaces all spaces with nothing so your name and list value will be exactly same. If while doing this, cell B2 value is not selected already, you may get an error saying this formula evaluates into error as above. If your formula is correct, you can safely click Yes.

Done. Check the validation in the cells. You must have got cascading lists as below.



This method is less cumbersome and very effective than using IF function. Likewise, you can use  SUBSTITUTE function to remove any additional characters in the list items to exactly match with the Named Range names.

A potential problem with cascading lists is that if first option is not selected then the second validation formula results into an error (refer to error screen above) and drop down will not work until one of the option in first validation is selected. Though sometimes this may be what exactly you want, in this case validation will not restrict user entering anything he/she wish in second validation.

Hope this post helped you understanding this concept better. Remember, learning in Excel is mostly about doing trial and error. Try it yourself and surely, you will be amazed with what you discover. Enjoy learning!!

Data Validation - Cascading Lists (using IF function)

We have been seeing a series of posts on Data Validation in our earlier posts. Today, let’s see another post on the same topic with an example on how to use dependent lists in data validation.

Here is a situation. I have an Excel template designed to capture personal details of candidates attending interview in my office. I have couple of cells with drop down lists to get location of the candidate. I also have a database of the countries, regions and states somewhere in the same sheet. Based on what we learnt in previous posts, we can do fairly well in creating a validation with these lists. Here is my template (A1 to B3) and database below it. To keep the example simple and presentable, I only took 2 countries and few states. I will leave it to you to imagine complex data.

See how the options in drop downs appear once I apply validation.





However, no matter what country I select in cell B1, list of regions available in cell B2 will be same. Same with B3, list of states will appear same. Now think of a user by mistake selects India as country and Alabama as the state. It’s Possible, right?

Think of it, would it not be better if I select India in cell B1, B2 list will be changed to have only regions that are related to India, and based on B2 selection, B3 drop down changes automatically to show only related options? Of course, it will be very nice. It will not only appear neat and tidy, it also eliminates the incorrect combinations that user might select. This is what I call Dependent Lists or Cascading Lists. Fortunately, Excel supports this and we are going to see how.

Cascading Lists are possible in at least couple of ways depending on the context and complexity of data. Let’s see one of the options for fairly simple data as in above example.


Using IF function to select multiple ranges

Cell B1 validation will be same as how you do normally. It has 2 options i.e. India and USA.
=$A$7:$A$8
We know that we can enter a formula for source in List option under Data Validation. We are going to use a formula to select either Indian Regions or USA regions. In Cell B2 validation, enter the following formula.
=IF($B$1="India",$B$7:$B$10,$B$11:$B$14)
If user chooses ‘India’ in cell B1, then the dropdown source is taken as B7:B10 which is where I have Indian regions. Else, B11:B14 where we have USA regions. Same logic for cell B3, we will enter the below formula to have change source as per B1.
=IF($B$1="India",$C$7:$C$13,$C$14:$C$23)
I have made B3 data dependent on B1 option. You can modify this to be dependent on cell B2. Since we have only 2 countries in B1, one IF function is enough. However if you want to to make B3 dependent on B2, since there are 4 regions options for each country, you will have to nest multiple IF functions. I am assuming 2 states in order under each region in the below example, meaning Andhra & Arunachal under South India and so on. Mind you, geographically this is not correct. Formula in data validation will be as below, note the nesting of IF functions.
=IF($B$2="South India",$C$7:$C$8,IF($B$2="North India",$C$9:$C$10,IF($B$2="East India",$C$11:$C$12,$C$13)))
You can simplify the appearance of this formula a bit by using Named Ranges. Refer to our earlier post on how to name ranges.

I will name both countries together as CList and Indian regions as IndList and USA regions as USAList. Also, I will name the states under each region like Andhra & Arunachal as SIndList, Assam & Bihar as NIndList and so on so forth. Below is how my formulas look like.
In Cell B1: =CountryList
In Cell B2: =IF($B$1="India”,IndList,USAList)
In Cell B3: = IF($B$2="South India",SIndList,IF($B$2="North India”,NIndList,IF($B$2="East India",EIndList,WIndList)))
After entering above formulas, see how the drop downs appear, once I select respective options.




I will be the first one to admit, using IF function will be very tedious if you have complex data. In the nextpost we shall see one more way to achieve the same but looking less messy. Stay tuned!!