Roll with it, baby

In MSXL 2003 which I will use for this example, the form controls can be made available through right-clicking one of your icon bars on the top or bottom and simply select the "Forms" entry from the appearing list.

In MSXL 2007 the way is a little different. You have to click on the Office button on the top left corner of the program, then choose Excel Options. Click the Popular section and tick the checkbox where it says "Show Developer tab in the Ribbon". The form tools are hidden in the second section of the developer ribbon through the button "Insert".

Okay, folks, let's imagine we have a table like the following:

Display Excel tables online >> Excel Jeanie HTML 4

Just make sure that we have an ID column in column A because this is necessary for the OFFSET function later on.

What we would like to achieve is that below this table we could use a scrollbar to go through the countries name in different languages. We will use a) the scroll bar to avoid manual entry and b) the OFFSET function to return the results. Don't get what I want? Don't worry, it's not to complicated and you could use it for various purposes. By the way, sorry for the missing special characters in the several languages, I just wanted to make sure, everyone can read this post without the need to install new fonts or languages to his / her system.

The first step would be to define a counter or something similar. We will do this in cell B11. I give it a nice blue shading and a small black border just to make sure that this is recognizable as the cell we will be working with later on when using OFFSET. Directly below that cell in B12 I set up a horizontal scroll bar by choosing the 6th icon from the right on my forms toolbar (ICON 1) and - with my mouse pointer now showing a little crosshair - draw a scroll bar into the cell giving it the width of that cell.

Make sure, that the six white points for resizing the scroll bar are still available (as in the picture above) and right-click the element and choose Format Control from the upcoming context menu. In the appearing dialog box go to the Control tab and enter the following values:

• Minimum value - 1
• Maximum value - 4 (because we have 4 languages to scroll through)
• Incremental change - 1

Into the cell link text field enter the reference of the cell which will control the country display in one of the four languages shown later on. In our case this is our recently created cell B11 so place the cursor in the text field and simply click cell B11. The dialog box should look something like this:

Click OK in the dialog box and click somewhere in your table to deactivate the resize handles of the scroll bar. If you now use the left and right buttons of the scroll bar the value in B11 should change in a range between 1 and 4. Now go to cell B14 and enter the following formula:

=OFFSET(A2,0,$B$11)

This will do the following:

• OFFSET(A2, - In general OFFSET will use the first element in the function (in our case A2) and return the cell reference of the upcoming functional elements (in our case rows as first element and columns as a seconde one).
• 0, - We don't want to offset any rows here in this example therefore we enter 0
• $B$11) - This will offset the cell reference by the number which it finds in our "control cell" B11. If you don't want to enter the $ charcter yourself, simply enter B11 and press the F4 key on your keyboard once. This so called absolute reference is important, because if we in the next step copy the formula, we don't want our control cell to change.

The result of this formula should be the value "Germany" in cell B14. If you now use the scroll bar it should go through the four languages. Now simply copy the formula down to cell B21. You can now scroll through the German, English, French and Spanish version of the G8 nations.

This may seem as a very complicated approach for this small table, but just imagine that you are creating a more complicated spreadsheet document and you don't want the user to enter values for languages manually. You simply could then protect the worksheet. After that, the scroll bar as a graphical user interface is still available but the user (your boss maybe?? ) cannot enter any wrong values into the cells.Of course you could use other conrols as well just depending on what makes most sense for you.

Here is the file, if you want to play around with Scroll Bars and Offset (ZIP - 3kB)

If you like this approach, leave a comment or tell me, how you would use this way of altering values?

Discuss this blog post in the forums (click the blue button)