VBA - Analyse The Recorded Code

Hi guys, welcome back for another session on VBA. In our previous post, we recorded a macro and executed to see how it works. In this post, let’s see the code behind that macro and analyse what’s going on behind the curtains.

See the Code of MyFirstMacro & Analyse

Open your VBA learning workbook that we saved with MyFirstMacro. Press Alt+F11. This should open up the VBE window. (Read about VBE appearance here).

In Project Explorer window, you can notice that Excel created a new folder Modules with a plus sign next to it. Clicking on that sign or double clicking on folder name shows you the child item under that folder which is Module1. If you are unable to view Project Explorer window, access it from View menu of VBE window.

Double click Module1 and your code window will be opened on the right hand side. I pasted the code below (coloured part). Have a quick look and we will analyse it line by line below. Remember, we have recorded MyFirstMacro using absolute references.

Sub MyFirstMacro()
'
' MyFirstMacro Macro
' This is my first macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'    Range("A1:A10").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

Let’s start analysing this code. As we analyse, we will also be discussing the code structure we should follow  in general, so follow closely.

Sub MyFirstMacro()

Every macro code should start with a word Sub. This word refers to Subroutine. It tells Excel that a code is going to follow and Excel has to perform task given in that code.

Next to Sub is the name we gave to our macro i.e. MyFirstMacro. Followed by this, there are open and close parenthesis (we know as brackets but get used to the word parentheses). Remember excel built in functions where we give arguments in parentheses? Here also the same logic follows. If you need any arguments as inputs to your macro, they can be given in this parentheses (we will see more about it as we move deeper into coding). If no arguments are required, just give empty parentheses.

Sub should be always be closed with End Sub at the end of the code. After this line, VBA stops executing the code for this macro. Notice the very last line of the code above.

' (Apostrophe)

Apostrophe is used by VBA to indicate a comment or information that need not be executed as code. Whatever you enter right side of apostrophe will not be executed by VBA. This character is used extensively by developers to explain the code. In our code, VBA enters the description, short-cut keys next to apostrophe.

Though we gave a short-cut key, code will have it just as information and handling the key press is Excel user interface's job and not VBA's job.

Range("A1:A10").Select

Pretty straight forward, isn’t it? We are selecting Range A1 to A10 here. But look at the construction. Range is an object; it has one of the method Select. We are using a dot after Range to call Range object related method i.e. Select.

Range takes an argument as input that refers to an Excel cell range. You can supply a real range like we have in this example i.e. A1:A10 within quotes or you can give a named range etc. as an argument.

Tip: Press F1 in VBA help and type "Range Object" in search. Once you read about "Range". Once this is done, also read about "Range Object Members".

With Selection.Interior untill End With

We already know that we have to follow a particular hierarchy to work with lower level objects. For example, in cell A1, I want to apply bold, italic and underline to the font. I will have to write 3 different lines of code with full hierarchy. It looks like below.

Range(“A1”).Font.Bold = True
Range(“A1”).Font.Italic = True
Range(“A1”).Font.Underline = True

We are only starting from Range object going 2 levels below. This is still very simple line. Think of drill-down from an application object level until some 8/9 levels down, each line will be lengthy and since VBA will have to interpret many number of drill-downs, it's performance would be slower in longer codes. So VBA has a workaround too. Above 3 lines can also be written like below.

With Range(“A1”).Font

   .Bold = True

   .Italic = true

   .Underline = true

End With

Here we are trying to combine like items under one qualifier With and once we are done using it closing it off by End With.

Selection is another object in VBA that represents the range of currently selected cells. Since while recording code we selected A1:A10, Selection indirectly represents Range(“A1:A10”). One of the methods of Selection object is Interior object. Interior is where the background colour is set. In this line of code, we are defining a With qualifier to work with interior of selected cells in next lines of code until End With code line.

Other lines below this Selection.Interior are essentially to set various fields required in Interior. What we are concerned is the line .Color = 255. This is where you are setting the colour required as a background of cell. Number 255 essentially used to represent red colour  You can read more about the numbers for all colours in VBA help.

With Selection.Borders(xlEdgeTop) till the end of the Code

All these lines are used to define various properties Excel requires to set border line. You can pretty much derive what each line is trying to set by reading the line. This code is in human readable form, right?

Having said that best way to learn VBA coding is to record a macro and analyse the code, there is a serious flaw with this method. At times, VBA tries to give larger code where there is no need for. This often makes code very lengthy and confusing.

For example, to apply colour to a cell, you need not select the cell. Remember our code line Range("A1:A10").Select? This line selects the range defined. But to work with a range in VBA, there is no need to select it. So instead of our original code;

Range("A1:A10").Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

We can also write

With Range(“A1:A10”).Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

Notice that I have merged first two lines of initial code in 1 line. This modified version works exactly same way but with fewer lines of code, there by improving quality of the code and speed of execution.

This is not the perfect coding either. You need not define values for some default properties unless we need to change them specifically. For example: to apply colour to a cell, we need not change its values for Pattern, TintAndShade etc as given in our original code. So our code can be further reduced like below.

With Range(“A1:A10”).Interior

        .Color = 255

End With

Needless to say, this snippet of code works exactly same as our lengthy version. So is this effective? No. When we are working with only one property as above, we need not use With qualifier. So final code will be;

Range(“A1:A10”).Interior.Color = 255

Our 8 line initial code is reduced to one simple and cute line. This line applies red background to cells A1:A10. On similar lines, let’s see how the lengthy 32 lines border related code can be condensed in 8 lines.

With Range("A1:A10")

        .Borders(xlEdgeLeft).LineStyle = xlContinuous

        .Borders(xlEdgeTop).LineStyle = xlContinuous

        .Borders(xlEdgeBottom).LineStyle = xlContinuous

        .Borders(xlEdgeRight).LineStyle = xlContinuous

        .Borders(xlInsideVertical).LineStyle = xlContinuous

        .Borders(xlInsideHorizontal).LineStyle = xlContinuous

End With

Now look at the code again, line by line. We are writing code to apply border on left, right, top, bottom, vertical & horizontal of each cell, all in separate lines. With a bit of research in VBA help, we would find that all borders are referred in a collection named as Borders. Setting a value to this collection will be applied to individual items in the collection.

Also see both the codes to apply colour and borders. Both works with Range(“A1:A10”) so you can combine both of them with the With qualifier as below.

With Range("A1:A10")

        .Interior.Color = 255

        .Borders.LineStyle = xlContinuous

End With

Started with a 40+ lines of code given by VBA recorder, we arrived at 4 lines which works exactly the same way but with increased efficiency. Go ahead and replace MyFirstMacro code with above snippet between Sub and End Sub and see if this works exactly same as before. (Or create a new macro with this code, you can compare better this way)

But still, without knowing head and tail of VBA, recording and analysing is the best option you have to manoeuvre this mammoth. Whenever you have time, go through the Object Hierarchy to know the short-cuts and techniques in using objects effectively.

See the Code of MySecondMacro & Analyse

We have recorded this second macro using relative references. In your VBE code window, scroll down the code until you see a horizontal line across the code after End Sub line of MyFirstMacro code. This horizontal line represents starting of another macro. You will be able to see Sub MySecondMacro() immediately below this line.

Compare the code of MyFirstMacro and MySecondMacro. You will notice only one difference in the code (ofcourse, apart from difference in name, description and short-cut key) i.e. MySecondMacro contains ActiveCell.Range("A1:A10").Select instead of Range("A1:A10").Select in MyFirstMacro.

What it means is simple, instead of hard coding range A1 to A10, MySecondMacro tells Excel to consider current active cell as the base cell and from there select 10 cells below. That is what relative reference is, right?

I trust we are clear on how to analyse the code. Once you know what the recorded code means, it only takes changing it to suit your requirement. For example, in our code to apply background colour, change the number 255 to something you wish and see what happens. Whenever there is an error (if you enter a value outside allowed range), don’t forget to consult VBA Help to see why. It will be fun to see how the changed values effects the background colour of the cell. See it yourself!

We will have many opportunities to write codes to achieve different tasks in Excel. We will analyse tons of code during those times. Before diving head first into the serious coding, we will see some good practises you should follow while writing VBA code. Stay tuned for next post!!

VBA - Record A Macro

It's been a while we touched VBA. Let's do a quick recap on what we discussed on this topic so far. VBA is an object oriented language, everything inside Excel is treated as an object. Objects have properties, methods and events. You can change the attributes of objects by changing its properties, you can perform actions using methods of objects.

Excel has an object structure called Object Model. We need to follow this structured hierarchy to reach the object we want to work with. We also saw the short-cuts to reach the desired object without following the lengthy code lines.

Enough already, let's follow the best way to learn VBA coding i.e. using the built-in recorder to analyse code. So we will go ahead now and record a macro first. What actions should we record? How about recording selecting few cells and colouring them in bright red and apply border to them.

Let’s create a new workbook and save it as VBA Learning and select Excel Macro-Enabled Workbook in Save as type field (only in Excel 2007 or later). We will use this book to do all our exercises going forward.

Record Your First Macro

In Excel 2007 or later: Click on View ribbon > Macros drop-down button > Record Macro

In Excel 2003 or earlier: Click on Tools menu > Macro > Record New Macro

This action opens up a dialogue box named Record Macro. Before moving on to record a macro let's analyse what this box contains.

Record Macro box in Excel 2007

Macro Name: You can provide a name to the macro you are recording. By default, names given by Excel are Macro1, Macro2 etc. You can define a new name that says what the code does. Remember, you have to follow couple of rules while naming the macro. Just like Named Ranges, name should always begin with a letter or underscore. Name should not contain spaces or invalid characters in-between. There are few names reserved by Excel so you should not use them. Macro names which are already used with in the same workbook will also be not allowed.

Shortcut key: Assign a key combination so that you can just press them and your macro is run. You can avoid multiple clicks to access the macro this way. You can use a combination of Ctrl, Shift and any one number, alphabet or special symbol keys on your keyboard, Ctrl key is to be used mandatorily. But remember to be careful while selecting your short-cut keys. Macro short-cut keys override Excel built-in functionality of those keys. For example, if you use Ctrl+S as short-cut key for your macro, then pressing these keys will always run the macro within this workbook but you will lose the Excel save command using Ctrl+S.

Store macro in: This is a drop-down list having three options. You have option to save the macro in ThisWorkbook, New Workbook or Personal Macro Workbook.

This Workbook refers to the workbook you are trying to create the macro. You will use this option if you want to keep the code you are writing in the workbook you are working in.

As the name suggests, New Workbook refers to a new workbook. Code will be saved under a new workbook that will be created once you record the macro.

Selecting Personal Macro Workbook keeps the code in a central workbook. Code saved this way will be available across any of the Excel file that you open.

Description: Enter a quick overview of your macro for future reference. You can enter anything you wish here and this field is not mandatory. If you are using Excel 2003, you can see a line of description already entered by Excel. In my system, it is this line: Macro recorded 21/02/2014 by Hari Krishna Popuri.

Fine! Let’s fill these fields with our data.

1. Enter "Macro Name" as MyFirstMacro (I am avoiding spaces; you can also name something like My_First_Macro, but decide one way and have a standard naming convention across your projects. It looks neat)
2. For "Shortcut key", click in white space next to Ctrl+ and press Shift key and Q.
3. Let the "Store macro in" field contain This Workbook
4. Enter a "Description", This is my first macro.

Record Macro dialogue box should look like below at this point.

Click OK. Remember, from now on, all your actions in Excel will be recorded. So we have to do precisely what we want to do. Avoid unnecessary clicks here and there etc.

After clicking OK, you can notice a blue coloured button in Excel 2007 or later and a word "Recording" on the status bar in Excel 2003 or earlier.

2010 Screenshot

2003 Screenshot

This means your macro is being recorded. Excel 2003 will also show a new floating menu ‘Stop Recording’ with two buttons. One button is blue coloured like the one in Excel 2007 status bar. You can click this button to stop recording the macro. The other button is ‘Relative Reference’. In Excel 2007, you can access this option from View ribbon > Macros drop-down button > Use Relative References.

Remember our post on Absolute and Relative references? In Macros, by default absolute references are used. You have to click this relative reference option if you want to use relative references so that the actions you recorded will be coded depending on the relative position of the active cell. For now, let’s stick to default option and see what happens.

Now that our macro is being recorded, click on cell A1 to A10, apply background colour red. Apply ‘All Borders’. My sheet now looks like below.

Click on “Stop Recording” button now (or blue button in Excel 2007). You have successfully recorded a macro.

Now let’s test it. We used Absolute References while recording our macro. So every time you run this macro, no matter which cell is selected currently, cells A1:A10 of active sheet will always be coloured in red and bordered.

To test this;

1.  Go to Sheet2 and click on cell B10 (or anywhere you like)
2. Run the macro by pressing key combination we chose (Ctrl+Shift+Q). You can also go by menu/ribbon way.

If everything goes fine, cells A1:A10 in Sheet2 must have been coloured in red and bordered. Try this with different cells selected but result should always be same. Do you also see this way?

Use Relative References 

Let's record another macro using relative references. We will name it MySecondMacro

1. Click on cell A1
2. Click on View > Macros > Relative References
3. Select cell A1:A10, record another macro with name MySecondMacro
4. Give shortcut of Ctrl+Shift+W
5. Enter description of your choice

Now let’s test it. We used relative references and cell A1 was selected before recording macro. Once the macro started recording, from A1 we selected 10 cells below and coloured them red and bordered. So every time you run this macro, wherever the cursor is that cell and 10 cells below should be coloured and bordered.

To test this; 

1. Go to Sheet2 and click on cell B2
2. Run the macro by pressing key combination we chose (Ctrl+Shift+W). You can also go by menu/ribbon way.

If everything goes fine, cells B2:B11 in sheet2 must have been coloured in red and bordered. Try selecting different cells and check.

By recording your actions, you can automate most of the tasks without even knowing any code. In the next post, we will see the code behind these 2 macros and analyse it to get better understanding on what’s happening behind the curtains. Bye till then!!

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 2^nd 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 1^st 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 2^nd (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!!

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!!