September 28, 2008

Checklist Maker

This is a file I thought might come in useful at work. Here it is when you open it.

After entering a suitable title in cell D2, start to enter items starting from (and including) cell D4 downwards. When you that the item number appears in column B and a drop down (data validation) list appears in column C. The default value is X (unchecked). If you choose O (checked), the item row cells, (columns B,C and D) change color as an easy means of identification.

You can have spaces between the rows. In the following image, I have widened the size of column A and added some dummy headers. Even with spaces, the code will produce the correct item number in column B.

After your checklist is ready, you can copy it to another workbook using Excel's built-in commands on the sheet tab right click menu. Then you can choose to delete the checklist code or comment it out. The latter might be better if you might need to make changes in future.

Download it here :-)

Posted by andrewe at 13:28

September 06, 2008

Quick Filter Updated

Quick Filter has new some new buttons.

If you have used it before, you would remember that you could jump from item to item by double clicking the list below.

By this, I mean that if you had the same item several times in a row or column, you could jump to each instance, changing the order in which way you jump by using the Previous and Next options.

You could add navigate to adjacent rows or columns, even when hidden, by pushing the appropriate Up, Down, Left and Right buttons. Doing so would refresh the list, but you could refresh manually if you edited some items since the form was displayed (being modeless for Excel 2000, you can select and edit cells even while the form is displayed)

Not forgetting the Sort button, the form was a "filter" of sorts and quick to use, hence the name. I liked the fact that I could use it both horizontally and vertically, something that the regular filter could not.

So what is new? I have added a button to "Filter" a selected item in the list. This very basic capability will hide all other rows or columns, depending whether the Horizontal Filter or Vertical Filter options are used. Not much of a feature? Well, you can also apply the filter on a group of selected cells, instead of an entire row or column if you prefer. And also, already hidden rows and columns before filtering will stay that way when you push the Show All button. That makes it a little more interesting :-)

Quick Filter is available with 2 of my downloads.

JMT Excel Utilities

New Book Navigator

Hope it comes in useful!  
Posted by andrewe at 10:45