Saturday 1 March 2014

Creating Validation Lists / Drop down lists

In this post I will take you through creating validation lists easily without too much effort and without VBA.
For those who are wondering what is VBA it is code that Excel or Microsoft Applications let you write so that you can automate your processes.

? When would you use a Validation list / drop down list?

- If you are creating a document that many users may need to add information to and you'd like to restrict the data they input into a certain cell
- If you're working on a range of cells where only a limited amount of data needs to be entered repetitively

A few examples are, if you need only Yes / No answers or if you need a customized list of items.

In a sheet enter a list of items that you would want to see on your validation list.

In the snapshot below I've used Items1 to 12 for my list. An easy way for you to create this list is to type Item1, Item2 and Item3 in 3 consecutive cells in you worksheet and select the 3 cells move your cursor to the lower edge of the 3 cells till you see a "+" sign and then simply drag it for the number of cells that you would need for your list.
Excel automatically increments the number for the number of cells that you've dragged.




Select the Cell on which you would like to apply this validation and then click on the Data tab > DataValidation and select DataValidation from the menu.

You will see a settings tab within which you can select List from within the Validation Criteria. 
In the Source you can enter a range of cells in this case it is for cells B7 to B18.



You have the option of adding an Input Message to inform the use what kind of data these cells will accept. In this case we've added a Title of "Select Items" and the Input Message of "Select Items from pre selected list.". You can add your own message title or message. This will show up when the cells with the validation list is selected (see Image above with the Input message).



In case the user enters an wrong value which your validation list will not accept you can add a Error Alert message in the Error Alert tab. You can change your Icon style in your error alert by choosing one in the style drop down. Excel give you a preview of what the Error Icon will look like. In this example below we've used a Stop which is a white cross enclosed in a red circle.


The error alert is displayed in the image below.



Finally the validation list is complete(see the Image below).



There are other options for a validation list other than picking it up from a range of cells as you can see in the image below.


You can even add a formula for this example below the list allows the user to enter any number from 1900 to 2000 and also lets the user add "NA" into the cell B36.

=OR(AND(B36>= 1900,B36<=2000),B36="NA")





Using this you can easily create a list which users cannot tamper with and will only accept values which you require.

Hope this makes Validation lists a bit more simpler. If you have any questions please leave me a comment and I will be happy to get back to you.








Sunday 16 February 2014

Array formulas demystified

The first time I saw an array formula I was amazed at the power of it. I was working on a scheduling package and wanted to get total number of hours for each employee within a list of around 125 employees. I thought I would need to write VBA code to get the totals which was going to take some time and may have been prone to some bugs and errors. A colleague introduced me to array formulas and from then on there was very little that could not be achieved using an array formula to get results quick without writing code.

? Where are you likely to use an Array formula ?

Array formulas are used if you need to pickup aggregate information based on a list of items.

In the example below we are picking up sum of Total Costs for an item within a list.

An array formula can be identified by the {} braces at the start and the beginning of the formula.


You start of by writing your formula as you would normally do within a cell but once completed
instead of clicking the Enter key you press a key combination of Ctrl+Shift+Enter.

This adds the {} braces to your formula and lets Excel know that it is going to be working on some condition and need to return results based on some criteria.

Explaining the formula above.

$D$4:$D$16 = $D19 

This is similar for a for each loop iterating through all the elements within a array and returning a value of true if the value in cell D19 is equal to the value in the range $D$4:$D$16. Only in this case the value returned is the corresponding value of the cell in the range $O$4:$O$16. 

In the example above this would return an array of {585,0,0,0,0,0,1170,0,0,585,0,0,0} which if summed up will give us a total of 2340. 

Voila the power of Array Formulas. And all without writing a single line of code.


                              If you edit the formula and click on Enter instead of Ctrl+Shift+Enter combination this will just create a normal formula without the {} braces and will not give you the required result giving you a #Value! error message in your cell.

                               As you can see in the Image below this gives you the sum of costs for the Device "MS-9200UDLS". By dragging the formula for the rest of the devices the sum of costs for each of the devices is picked up without the need for writing code using for loops or do while loops etc.


Now if you use your ingenuity you can also get the average using the same method. Try it out yourself and if you need a hint check the Image below for the solution.


                   A few things to keep in mind is the range that you are using to create your array formula should be a finite range and also the column that you are using for the condition and the column that you need to pick up the value from to sum up should be of the same range in the example above the range is from Row 4 to 16.

Hoping this has helped to demystify array formulas for you and help you and amaze your colleagues at work.










Saturday 15 February 2014

Welcome!!!

Welcome everybody!!!

I've finally gotten down to writing my own blog. This space is dedicated to software solutions for businesses big and small, professionals and everyone else really.

Apart from that, I will also share tips and tutorials to help you better your MS Excel skills and much, much more.

Stay tuned for more and if there is anything you'd like help with, leave us a comment and let us know and we'll find a solution for you.