In Excel, the Like operator is used with ? and * to designate one or many missing characters, respectively. Someone recently asked me about using the Like operator in MSQuery. Since I didn’t know off the top of my head, I went to the help file. If there’s anything in the help file about this, I couldn’t find it.

In MSQuery, Like works just how you’d expect it to, that is if you’ve used Like in some other program. The difference is that MSQuery’s Like appears to have only one wildcard character, namely the percent sign (%). It stands in for multiple characters, similar to the asterisks (*) in Excel. The criterion

will return all the records that start with”KU”. Like appears to be case insensitive. That is, the above criterion would also return records that start with “ku”, “Ku”, and “kU”.

To use Like in a parameter, simply concatenate the percent sign with your parameter with the ampersand (&). For example, to find all the customers whose CustomerID started with ‘S’ and whose City contained a certain letter, you might set up your criteria like this:

This uses the Northwind sample database’s Customer table. I entered an ‘a’ when the parameter prompted me and got all the CustomerIDs starting with ‘S’ whose City contained an ‘a’.

It is possible to change a Chart’s Source Data by resizing individual Chart Points.

Let’s say you’ve got a Column Chart and you want to change one of the column sizes.
Click once on the column. This selects the Series. Click again on the column. This selects the single column (Series Point).
It’s not a double-click… but a click-wait-click action.

You’ll notice some blobs appear at the corners of the column.
An extra one is also positioned at the top of the column – this blob is special.
You can click-drag it to resize the column and it will change the Source Data.

In the following example, there are some people who work a number of hours per week, of which a portion of that time is devoted to projects.
I’ve graphed the formula column non-project work. The formula in D2 is =B2-C2

I can hear you saying:
Hey! When you resize that column, it’s going to overwrite your formulas!

Excel is pretty smart and knows it’s a formula, so it throws up Goal Seek.

In this case I’ve told Goal Seek to keep changing the “Project hours” until “Non-Project hours” becomes 30.
Project hours gets set to 5 and we’re happy!

Similar to using Conditional Data Validation, you may have a need to change the contents of one listbox based on what the user selects in another listbox. Assume you have two listboxes on a userform: one for sides and one for players. When the user selects a side, the list of players changes. It will look like this:

Start with three named ranges. The first name is “Sides” and covers A1:A2 (which contain Chelsea and ManU). The other two range names are “Chelsea” and “ManU” and cover the lists of players in columns B and C. It’s important for this example that the named ranges for the players exactly match the values in A1 and A2. You can set up a situation where they don’t match, but it takes a bit of extra programming.

The code behind the userform is pretty simple. In the Initialize event, you load up the Sides listbox. Then, whenever the user changes the value of that listbox, you load up the Players listbox.

Microsoft’s KB article 161518 discusses this very issue. I have two problems with their solutions: First, it appears they use the RowSource property. I never use this property, so if you’re like me their solution won’t be optimal. I don’t have anything against RowSource, I just like the flexibility that List, AddItem, and RemoveItem afford. Second, the code sample they provide looks like this

when viewed in FireFox. Well, the code on my site can be viewed from any browser. You still can’t copy and paste it if it contains quotes, but at least you can read it.

From Jim Jazwiecki:

An Excel quiz. Look at ten words and decide if the word is an Excel function or the name of a foreign currency. I didn’t do so well. I could identify three Excel functions. One of the function was WEIBULL. I was a little skeptical, so I had to look it up in help.

Returns the Weibull distribution. Use this distribution in reliability analysis, such as calculating a device’s mean time to failure.

No, YOU use it in reliability analysis. I’m perfectly happy just fixing the device when it breaks.

It’s pretty handy to right-click, drag on the Fill Handle to get a secret list of options.
Use this Site’s Search link for “Secrets of the Fill Handle” – a previous post covers right-clicking.

Here are MORE of the goodies the Fill Handle allows:

Hold down the Ctrl key while dragging the Fill Handle. The mouse cursor gets a baby plus indicating Copy mode.
It performs an AutoFill Copy.

Hold down the Shift key while dragging the Fill Handle down. The mouse cursor changes to a double line with double arrows pointing outward indicating Insert mode.
It performs Cell Insert.

Hold down the Shift key while dragging the Fill Handle overtop of the selected cells. The mouse cursor changes to a double line with double arrows pointing inward indicating Delete mode.
It performs Cell Delete.

Of course, these actions work when the entire Row or Column are selected too!
(Though, if you’ve got entire Rows or Columns selected, you might find it quicker to hit the shortcut Ctrl+NumKeypad_Plus or Ctrl+NumKeypad_Minus)

Whenever I make major changes to the layout, I like to put a post out there so you have an easy way to comment on it. You can always send an email too. You may notice that I’ve made some changes to the menu (sidebar). Namely,

• Recent “x” lists are now javascript expandable lists
• Categories are now in a dropdown and alphabetical and hierarchical
• The archive list is gone and replaced with a link to an archive page

Expandable Lists

In style.css under the only theme I use, I added this

I copied the “ul ul” right above it because I wanted all the stuff to be the same, I just wanted a way to differentiate between menu lists that I wanted to hide and those I didn’t. The “display:none” line is the only difference between this and the one above it. All of this is based on the Gemini template.

Then in index.php, I added this between the already existing script tags. If you don’t have script tags you can but them within the head tags.

Then in rap div, I have this:

The function comes from Adrian Holovaty. It has too many arguments because I modified it from the original. I modified it because I never want an expanded list to shrink because another list is expanded.

By adding ‘class=”expand”‘ to the ul tag, I was able to choose which lists are expandable and which aren’t.

If you know anything about php and css, then you know that I don’t know anything. I’m happy to take your suggestions for the proper way to do this. If you don’t know anything, then assume this is the correct way at your own peril.

The Recent x lists are from Customizable Post Listings and Recent Comments/Recent Posts

Categories

The new categories dropdown is provided by Typedby.com.

Archives

I never use the date archives, so I’m happy that Jon pointed out that they suck. I’ve left the calendar part because it looks cool, but replaced the monthly listing with sortable archives provided by nicer-archives-for-wordpress. It looks pretty cool and I think will come in handy. I use the Search box almost exclusively. The Atomz search works really well and I can always find the post I want with a quick keyword search.

As always, tell me what you think. If I killed your favorite navigation method, be sure to speak up. We may be able to make a deal. With all this real estate savings the Google ads are now above the fold. I may be able to retire soon. Although I’m considering putting the search box up higher to get people to use it – it’s just that good.

Recently, I got this comment:

Dick,

Just browsing and I saw that Jason posted a link to a site that can unprotect projects.

[See the censored comment here]

I’m not sure if you edit/censor the comments, but I don’t like that site. (Yes, I’ve used it!)

But there are too many [Excel developers] who make their living with protected .xla projects.

Yours in censorship,

I hardly think it’s necessary to post the entire comment, but there were two things I loved about it: The admission that he’d used the site that he hates; and the closing “Yours in censorship”. They were just too funny not to share.

To answer the question, I’ve never censored a comment until this one. I want people, like Jason, to feel comfortable that they can comment on this site. Actually, I just don’t want the extra work of monitoring comments. Although I read them all, I don’t want to be the comment police.

I thought about this for a couple of days and I decided to remove the link that Jason provided. There’s no question that I am against people stealing other people’s work. There’s also no question that the method provided by the link could be used to do that. It could also be used to get at your own code for which you lost the password, a reason about which I have no objection. I don’t advocate the outlawing of CD-RWs because they may be used to steal music. So what’s the difference?

The difference is that I and other authors of this site are Excel developers. It is in our best interest that as few people as possible know how to crack code. If we were professional musicians, maybe we would be against CD-RWs. I’m not going to crusade to have that information removed from the internet, I simply choose not to spread it.

I imagine one or two of you has an opinion on this subject and I’d like to hear it. And to Jason: I’m sorry I censored you. I’m sure you meant nothing malicious and were only trying to help. You can call me a communist if you want (but nobody else can).