Daily Dose of Excel

That I started this blog four years ago. I can’t. It seems more like six and a half. Here’s some stats:

That’s about 26 posts per month and about 8 comments per day. I’m no Nick Hodge, but hey.

As is my wont, I’ll be having an anniversary give-away. I just like to give back to all the people who read, comment, and click on the advertisements. This year you get a choice between RibbonX: Customizing the Office 2007 Ribbon and Excel PivotTables Recipes. To enter

• Send me an email with a link to your favorite non-Excel blog, which book you prefer, and a subject of “Four More Years”.
• If you use a different subject or your email doesn’t contain a link or you don’t pick a prize, you’re out.
• I must receive the email by 12:00AM Central Daylight Time, April 7th, 2008.
• You must send the email from the same email account with which you registered on this site. If you’re not registered, you can’t win. If you used a fake email to register, just include that fake email address in your message so I can confirm you’re registered.
• This list of eligible entrants will be entered into an Excel spreadsheet, a random number will be entered next to his name, and the list will be sorted on the random number. Whoever is at the top, wins.
• I’ll probably list some of the more interesting blogs, but I won’t associate them with your name, so don’t be afraid to send in ParisHiltonWatch.com
• One email per registered reader.
• The winner will get to choose between a copy of RibbonX: Customizing the Office 2007 Ribbon or Excel PivotTables Recipes.
• To be eligible, the entrant must live in a place that Amazon.com will ship to and where the shipping will be less than the cost of the book.
• Any other rules that make this contest less of a burden on me or that I may choose to enact retroactively or otherwise makes the give-away legal and easy are hereby included.

Thanks for reading!

Finally, a legitimate use for Excel

Excel & Music

I get all of them except the “Milkshake” one.

I recently ran into a problem with an application I developed over a year ago. I had pre-formatted 100 rows in a template, some just formatting, some conditional formatting. “We’ll never need 100 rows”, I said. One hundred ninety-six rows later and I feel like the Patent Commissioner who said everything has already been invented.

Should I have picked 1000? Should I have formatted the whole column? I wasn’t trying to be stingy, I just doubled what I though was the most rows we’d ever use. What do you do?

Hi All,

As many of you know I give away a number of tools for Excel through my site www.jkp-ads.com. Today I have updated the two most popular downloads:

Name Manager (which I created together with Charles Williams, www.decisionmodels.com):

Most important change: the unused names filter now includes objects in its search, as well as VBA code. It makes the filter much slower, but way more useful in my opinion. Also, I have added the Greek character set so Name Manager doesn’t (wrongfully) think range names with Greek characters are corrupt.

So far, Name Manager has been downloaded about 50,000 times since I posted it on my site.

Flexfind

I have updated the user interface of Flexfind so (in my opinion) it is easier to use. Also, I have mimicked the find all behaviour of Excel: if you select multiple items in the found items list, Flexfind will create a (multiple) selection of areas of the found cells.

Flexfind is less popular than Name Manager, the download count is at about 23,000.

Enjoy!

Of course I am open to any comments, suggestions and -most importantly- lots of praise

Regards,
Jan Karel Pieterse
www.jkp-ads.com

Someone sent me an email last week suggesting I have a Wishlist category where we could discuss features we hope MS will someday incorporate into Excel. I think that’s a fine idea. If you like, send me an email with your wish. If I find it sufficiently interesting, I’ll post it for others to comment.

For my first wish, I’d like a sheet in my workbook dedicated to External Data. I think someone on Simon’s blog suggested this in a comment once (maybe Harlan). The worksheet would be a recordset of an external data source and nothing else. Users would not be able to type anything outside the recordset or change much about the sheet. Here’s the kicker: Excel would keep the sheet and the external data source in sync. When you write to a cell on the sheet, the external data would be updated. If the recordset isn’t updateable, you wouldn’t be able to write to the cells. If you try to put a string in a cell that’s linked to an external data field designated Integer, you would get an error. Basically it would be like datasheet view in Access. Now I handle all this will class modules and ado, but it would be nice to stay within Excel’s object model to accomplish the same thing. Not to mention the UI.

One prediction I made at the recent Excel Users Conference in Sydney is that Excel and Access will be one product in our lifetime (well mine anyway). Have you seen Access 2007? Do those dropdowns in datasheet view look familiar? “But Access is a database”, you say. No, not really. Access is the front end to a database. In my world, Excel is usually the front end to a database. It’s not that I don’t like Access, there are just people who are better at it than me, so I generally avoid that kind of work. The recent datasheet view changes in Access 2007 brings that prediction one step closer. My external data worksheet wish would bring it closer still. The only question that remains is whether to call it Accel or Excess. I prefer the latter.

You can leave your own wish in a comment (or email as noted above), comment on my wish, or leave a comment with what you would name the new combination Excel/Access product.

Red wants to have a kind-of lottery for his students. He will award them prizes based on a random drawing, but wants to weight each student based on the number of assignments turned in. Normally, I would accomplish this by typing the name of each student in column A one time for every assignment he turned in. If John turned in three assignments, I'd type his name three times. In column B, I'd put a RAND() function and fill it down. Then I'd sort by column B. I'd get something that looks like this:

That's all well and good, but it's missing a few things. If I'm giving away three prizes, Sue wins them all unless I manually exclude her. But the worst part is that it happens too fast. Excel calculates so fast that it's not entertaining to calculate in front of a group (unless you're Charles Williams, of course). I wanted to come up with something that doesn't allow ties, calculates more slowly, and is generally more friendly. Here's my first stab

Behind the button, I have this code:

The user enters the information in columns A and B, up to 30 students. The code fills column I with one instance of each name for each assignment. Then for each name it fills in a random number between 0 and 999. For show, it fills each cell 50 times to make it look like it's really doing some work.

In column C, I have this array formula

=MAX((rNames=A2)*(rDraws))

and in column D, this

=IF(RANK(C2,$C$2:$C$31)>3,"",CHOOSE(RANK(C2,$C$2:$C$31),"1st Place","2nd Place","3rd Place"))

There's not a lot of error checking and far too many literals in the code, but it's a start.

Download Lotter.zip. Yes, it's 2003 format.