Monthly Archives: June 2013

Interactive Excel Voting Board

If you run a training session, or perhaps you chair a meeting, you want to get some instant feedback, or perhaps you want attendees to vote on something. You can do a simple show of hands which works fine, but how about something a little more high tech but without going overboard?

Ideally, for this to work, and in particular to get people involved you will need a touch screen. You could just use a mouse on the computer but this seems to work quite well and gets people out of their seats participating in the session.

There are two ways I can do this:

  • The non VBA way
  • Using VBA

Let’s start with the non VBA way using FORM CONTROLS.

If you are using 2003 then you will need to activate your FORMS toolbar. Go to VIEW, TOOLBARS and select FORMS. Do not use CONTROL TOOLBOX which looks very similar as this requires the use of VBA to make things work.

If you are using 2007 or later, turn on the DEVELOPER tab and you will find FORM CONTROLS under INSERT in the CONTROLS group.

ScreenHunter_109 Jun. 14 12.07

Select the SPIN BUTTON option. Your cursor will change to a small cross-hair. Click and drag somewhere in your worksheet to “draw” the SPIN BUTTON. You can move and resize this at any time if it’s not quite right so don’t worry if it does not look quite right yet.

ScreenHunter_109 Jun. 14 12.08

Right click on the new SPIN BUTTON and select FORMAT CONTROL.

ScreenHunter_110 Jun. 14 12.08

In the dialog box decide what your minimum and maximum are and also the increment that each click of a button will increase/decrease the value by. So in this example I will set the minimum to 0, the maximum to 10 and the increment value to 1.

The final thing you need to set is a reference cell or CELL LINK. The form controls do nothing other than look pretty. In order for them to work, you have to link them to a cell and it is this cell that will do the work.

ScreenHunter_110 Jun. 14 12.27

Click on OK.

Now create a chart based on the value in the CELL LINK.

Format your chart whichever way you want and repeat as many times as necessary to create multiple charts to display people’s votes.

ScreenHunter_110 Jun. 14 12.33

Now for the VBA version. If you are familiar with VBA then this is a very simple way of doing things, and if you aren’t familiar then this is simple enough to have a go.

The benefit of VBA over the FORM CONTROLS is you have more options available to you and you can, if you want to, go mad and program it to do all kinds of weird and wonderful stuff…but for now, here is a simple example for you which you can embellish as your VBA skills grow.

This time start by entering the value 0 in a cell.

Now add a button to the worksheet, but this time use the ACTIVE X CONTROLS, NOT the form controls we used in the previous example.

Click on the BUTTON icon then click and drag to “draw” a button on the worksheet. Again this can be moved at a later stage if it’s not in the correct position.

Click on the PROPERTIES button and do the following:

  • Give a suitable name to your button (helps when writing code)
  • Enter a caption i.e. what you want to display on the button face

There are many more options you can go for but this would be the bare minimum at this stage.

ScreenHunter_110 Jun. 14 15.10

Close the PROPERTIES window for now.

Now we need to add some code to our button to make it work. Double click the newly added button, making sure that you are in DESIGN mode in order to be able to make changes to your button.

ScreenHunter_110 Jun. 14 15.15

 

I declare a PRIVATE variable in my GENERAL DECLARATIONS area at the top of my code sheet. I need this to store the value of the cell that I am associating with the button and subsequent chart.

I can then add the following bit of code:

ScreenHunter_110 Jun. 14 15.23

 

Again, repeat as many times as necessary to build up your voting board.

So far though, we only have a button that increases the value of the cell. We now need to add one that takes the value down by one each time we click on it. At this point you are probably thinking that this is a bit long winded, considering we had a single button that did both before. Using this method means you can have a more customised look and feel to your voting board rather than just up/down arrows (the choice is yours!)

Follow the same steps as before to add a new button and then add the following code:

ScreenHunter_110 Jun. 14 16.40

 

This time as well as decreasing the value of cell C6, I have added an IF statement to prevent negative values from being generated.

The final button you might want to add is reset button so you can quickly reset the value(s) to 0. This might be worth doing last of all once you have created all the voting buttons and charts you need and then have a single button to reset all the reference cells in one hit

.ScreenHunter_112 Jun. 19 16.15

 

Simply add extra cell references once you have set them all up e.g. Range(“C6, F6, I6”).Value = 0

Your final interactive voting tool will look something like this…

ScreenHunter_111 Jun. 19 16.14

 

So next time you want to have a poll in class or at a meeting you can do it with a little more style than just a show of hands.

Advertisements

Excel – Data Validation Error Checking & Other Useful Tips

In my blog “Using Data Validation to Control Data Entry”, we looked at how to set up DATA VALIDATION rules, add prompts and customise alert messages.

Now, let’s look at a few other features you might want to use when working with VALIDATION rules;

  • Finding cells that have DATA VALIDATION rules applied
  • Checking for data that no longer meets your rules
  • Preventing duplicate entries

Finding cells that have DATA VALIDATION rules applied

So, how do you find cells in a worksheet that have validation rules applied to them? You could click in each cell, one at a time and see if a drop down arrow appears. That’s fine as long as the validation involves a drop down list. But what if it has another type of rule or even a custom formula? No hints there!

For this, we need Go To Special. You can find this on the HOME tab under FIND & SELECT.

Go To Special Options

Go To Special Options

Click on DATA VALIDATION and any cells that have rules of any sort applied to them will be highlighted.

ScreenHunter_109 Jun. 04 15.25

The grey cells are those with rules applied (the labels in this example also happen to be grey – but it should be pretty obvious when you run this)

If you like your keyboard shortcuts, you can access this by the following combination:

Ctrl + G followed by Alt + S, followed by V and then press ENTER.

Checking for data that no longer meets your rules

As you create your rules, naturally you will test them and make sure they work. But sometimes you may make changes to rules after data has been entered. Unfortunately, Excel will not automatically highlight data that no longer meets your new rules. If you want to check how your data is affected by a new rule and whether it breaks it you need to run CIRCLE INVALID DATA.

Where to find Data Validation

If you love your keyboard shortcuts, then you’ll need:

Alt followed by A, followed by V, followed by I.

So make a change to your rule(s) first. Continuing our timesheet example, I set up a rule that limited the comments to 150 characters. I’ve now decided that even that is too much and want to limit it to only 100 characters. I make the necessary change to my rule, but there is already some data in my worksheet. In this example there is a comment that is 126 characters long. This was fine under the old rule but breaks my new one.

Now turn on your CIRCLE INVALID DATA.

When you turn this on, any cells that contain data that breaks the edited rule will be highlighted by a red circle.

ScreenHunter_109 Jun. 04 11.40

Unfortunately, Excel does not do this automatically for you, so you need to remember to do this after you have edited an existing rule. If applicable, make changes to the data in the cell(s) that are circled. Once they meet the new criteria, the circle will disappear.

Preventing duplicate entries

As well as controlling data entry through a set of simple rules you can use formulas to create complex rules with multiple conditions. One example where a formula can come in handy is preventing duplicate entries.

For this we need to use the CUSTOM option when creating our new rule within DATA VALIDATION.

In the FORMULA box enter the following formula:

=COUNTIF($A$1:$A$50,A1)<=1

What this does is check the selected range, in this example A1 to A50. Note the use of absolute references as you have to fix the range that is being checked.

NOTE: if you are applying the rule to an existing list make sure you apply the validation rule BEYOND the end of the list otherwise as you add new records no rule will have been applied to the blank cells and therefore allowing you to potentially add duplicate values.

The next bit A1 says look in cell A1 and check it against every cell in the selected range and count how many times it appears. This is left as a relative reference so that each cell is checked in turn when the formula is copied down the column.

The rule then states that when counted, each cell value must add up to 1 or less which means that if you add a duplicate value the count will be greater than 1 and therefore block it.

Let’s take this one step further…..

What if I want exceptions to the rule?

For that, all I need to do is modify my formula slightly….

=(COUNTIF($A$1:$A$50,A1)<=1)+(A1=“Dave”)

Essentially it is the same rule as before but with the addition of an exception. In this example I am allowing the name “Dave” to appear more than once. Note the use of brackets around the first part of the formula.

You can add further exceptions by adding more +(A1=”text”) on the end of the formula.

And there you have a few useful additional features from data validation!

Excel – Using Data Validation to Control Data Entry

They say that two things in life are unavoidable – tax and death. There is, however, one more you can add to the list, and that is people mucking up your spreadsheets! Send out a spreadsheet to five different people and you can guarantee you will get at least one person filling it out in some strange and obscure manner, and some bright spark won’t like your layout so they decide to re-arrange your table to suit them.

So how do you stop all of that? There are two basic things you can do:

  • Apply DATA VALIDATION to your tables
  • Apply varying degrees of PROTECTION to your worksheets and workbooks

Here, I am going to tackle DATA VALIDATION.

DATA VALIDATION allows you create drop down lists in cells as well as create rules that control what people can and cannot enter into a cell.

Let’s take a timesheet as an example…

Each week I want team members to fill out their own timesheet. They need to fill out all times for one week starting on a Monday through to Friday. Work is done on projects, and more specifically to tasks within a project. Time can be chargeable or non-chargeable to the project and different activities will fall into each of those categories. Team members are also required to add a justification where necessary, such as overtime. And finally, the timesheet needs to be approved by the manager.

So with all the different bits of information required, the possibilities for errors and creative form filling are endless.

A lot of this timesheet relies on specific project numbers and category descriptions. This sort of data entry is prone to errors, so use DATA VALIDATION to create drop down lists forcing users to select from a specific list of options, with no allowance for users to add their own values.

First, let’s create a list of team members so people can’t even get that wrong! Create a list (ideally on another sheet in the workbook – makes hiding the lists easier afterwards).  If you want, name your list e.g Team. This is not strictly necessary if you are using Excel 2010/2013 but makes life easier and is a MUST if using 2007 or earlier if your lists are on a separate sheet from the sheet where the drop downs will be used as you cannot select cells on another sheet unless they are part of a named range.

In your timesheet, click on the cell(s) that will have the drop down list. Assuming we have already created a list, go to the DATA tab and select DATA VALIDATION.

From the ALLOW options, select LIST.

In the SOURCE box use the range picker button to select the cells containing the list values, or if you have created a named range type = followed by the range name i.e. =Team (note: no speech marks).

ScreenHunter_109 May. 15 14.49

You should now have a fully working drop down list to choose from.

ScreenHunter_109 May. 15 15.00

To help your team use the drop down properly, you can add an optional prompt. Go to the INPUT MESSAGE tab, add a title of some sort and a useful message to assist (rather than confuse) the users.

ScreenHunter_109 May. 15 15.03

Click on OK to confirm. You should now see this next your cell.

ScreenHunter_109 May. 15 16.01

You also have the option to control whether people are restricted to using only the values in the list, or whether they can type in their own values. The latter option is potentially dangerous, but this may be necessary so a good user prompt here is a plus.

Go to the ERROR ALERT tab.

Under STYLE, you have 3 options;

  1. Stop – limits values to those in the drop down list ONLY (Good choice!)
  2. Warning – warns you are about to enter a value that is not in the list and asks if you wish to continue. To which you can say ‘yes’, therefore ignoring the list completely.
  3. Information – even less harsh than Warning. Tells you the value you are entering is not in the list. Click on OK to ignore and continue regardless.

There may well be times when you do want to give people the option to enter other values, which is fine, but if you want to cut out any form of creativity with your spreadsheet go for STOP!

If you don’t want the standard pop up message from Excel, type in your own title and message, so if someone enters something outside the list they get a custom message;

ScreenHunter_109 May. 15 16.19

Continue to apply drop down lists with input and error messages where appropriate.

DATA VALIDATION though is not limited to just creating lists. Create rules to control any of the following:

  • Whole numbers
  • Decimals
  • Dates
  • Times
  • Text length
  • Custom (create your own rules using formulas)

In each case you will have a variety of options: between, greater than, less than etc. Pick an option and enter values to suit your rules.

ScreenHunter_109 May. 17 15.19

Most of the options are self-explanatory but CUSTOM is where you can create formulas to create complex rules that may involve multiple cells. When using the CUSTOM function just be careful with your relative and absolute cell references especially when applying your validation rules to a block of cells. Although you apply the rule to a range of cells you are actually writing the formula for the first cell in the selected range and Excel copies the formula down/across to the other cells.

Eventually, you will get a complete timesheet looking something like this…

ScreenHunter_110 Jun. 04 16.40

So that is DATA VALIDATION. We now have a working spreadsheet where users have limited options on how to fill it out. However, there are still a number of issues that need to be dealt with here to further improve the reliability of the timesheet;

  • Incorrect project/task combinations
  • Incorrect status/details combinations
  • Late submission of the timesheet
  • Authorisation access

I will cover these in “Creating Linked Validation Drop Down Lists” and “Protecting Your Workbooks”. Further enhancements could be added using VBA…so by no means a done deal yet!

Also see “Data Validation Error Checking & other useful tips”