Archive for March 2008

Can You Believe

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.

Excel Pivot Table Recipes Excel Pivot Table Recipes

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!

Excel and Music

Finally, a legitimate use for Excel

Excel & Music

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

Limits

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?

Two popular tools updated.

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

External Data Worksheets

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.

Random Sorts

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:

Public Sub DrawNext()
   
    Dim rNames As Range, rCell As Range
    Dim rLastICell As Range
    Dim i As Long
   
    FillNames
   
    Set rLastICell = wshDraws.Range("I65536").End(xlUp).Offset(1, 0)
   
    If rLastICell.Row> 2 Then
        Set rLastICell = rLastICell.Offset(-1)
       
        Set rNames = wshDraws.Range("I2", rLastICell)
       
        For Each rCell In rNames.Cells
            For i = 1 To 50
                rCell.Offset(0, 1).Value = Rnd * 1000
            Next i
        Next rCell
    End If
   
End Sub
 
Private Sub FillNames()
   
    Dim rNames As Range
    Dim rCell As Range, rEntry As Range
    Dim i As Long, j As Long
   
    Set rNames = wshDraws.Range("I2")
    Set rEntry = wshDraws.Range("A2:A31")
   
    wshDraws.Range("I2:J65536").ClearContents
    j = 0
   
    For Each rCell In rEntry.Cells
        If Not IsEmpty(rCell.Value) Then
            For i = 1 To rCell.Offset(0, 1).Value
                rNames.Offset(j).Value = rCell.Value
                j = j + 1
            Next i
        End If
    Next rCell
   
End Sub

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.

Excel User Conference Download

Download Presenter Notes and Sample Files

If you didn't attend, these files will be of limited value. But there they are if you want them.

As I mentioned at the conference, I will be reformatting my laptop hard drive this weekend. My folder problem that I've been blaming on Groove, isn't Groove's fault (I think), but I'm due for a data enema anyway. I've downloaded Belarc Advisor to get a list of installed programs. I will only be installing Excel 2000, 22032003, and 2007. I eschewed 97 a while ago and I'm going to get rid of XP too. Any other tips before I embark on this magical journey?

Software to install (* means I want to know what you use):
Office 2000
Office 2003
Office 2007
TaxACT*
CDex
Canoscan
FileZilla
VB6
Foxit Reader
Alleycode
Aptana
InstantRails
StarCaddy
JetAudio
Firefox
RealVNC*
SnagIt
PDFCreator*
Irfanview*
VBScroll

Not making the cut:
Adobe Reader
Avast antivirus (I'm going without)
Copernic Desktop (any good desktop searches out there?)
Virtual PC (maybe next beta)
Lifecam (never use it, shouldn't have bought it)
Google Earth
Google Sketchup
Street and Trips*
VS.Net (I'll have to put it on eventually)
Subverstion
TortioseSVN

Conduct in Reserve

I'm back in the good ol' USA after traveling for 22 hours. I'm expecting a jet-lag hangover tomorrow. Today I had a 3.5 hour nap, so I'm still up at 11:30 writing a blog post. Here are some thoughts about my trip.

I got more out of the conference than the delegates. What a tremendously smart group of people. I was amazed at the questions they asked and the information I was able to pick up from them.

Lecturing is hard work. I was surprised how tired I was. But I learned a lot about the experience and I'm sure I will do a better job the next time.

I finally got to meet Charles Williams. In addition to sitting in on his excellent lectures, I really enjoyed spending time with him and his wife.

International travel is for people who can afford business class. I "updgraded" to Economy Plus on the way over and enjoyed the five extra inches, but fourteen hours on a plane was still a painful experience. Had I not had an empty seat next to me, my wife and I would have had the two middle seats in a 3-4-3 arrangement, which would have been brutal.

I attempted to starve myself of sleep before the trip. I slept only five to six hours per night for five days before I left hoping I would be so tired on the plane that staying awake would not be an option. I don't sleep on planes, and this was no exception. On my 14 hour flight, I probably slept three.

On the way over, I listened to 136 songs on my iPod. On the way back, eight-six. I watched some movies on the way back, all of which sucked. Don't bother watching Dan in Real Life, Martian Child, or August Rush. I also watched the first 10 minutes of Enchanted, but started getting a cavity, so I quit watching. On the way over, I watched Juno (not bad), and the first half of Elizabeth: The Gold Age three times. The latter kept stalling and they would start it from the beginning. I liked what I saw, but while watching the third time I swore it would be the last. After it stalled the third time (some sort of technical problem), they just played a different movie. I hope to see the rest of it someday.

Airplane food is bad. I know that if you saw a comedian in the 1980s you already know that, but I thought they fixed it by now. Runny eggs? Seriously? Just give me a bagel and some cream cheese and I'll be just as happy.

Inexplicably I only gained one pound despite eating and drinking with impunity. I ate kangaroo, baramundi, bay bugs, grilled prawns, and a host of other things. The beer over there is good. I drank Carlton, Victoria Bitter, some honey flavored thing, Coopers and a few others. I only saw a Fosters sign once and never saw an American beer. Guinness and Stella Artois seemed popular, though.

I mentioned in my last post that things were expensive in Australia. Everywhere I went I paid 2-3 times what I would have paid in the US. To be sure, I was in tourist areas a lot, but I would expect to get a reasonably priced meal in China Town, but didn't. I also went to a bar in downtown, but still paid AD7.00 for a beer (I later found out that James Squire is a premium beer). My choices for breakfast were $30 for a buffet at the hotel or $8 at McDonald's. A coke at a convenience store costs $3.

My wife and I went on a couple of ferry rides, toured the blue mountains, walked around the botanical gardens, and generally enjoyed Sydney. It is a spectacular town and we had a great time. I heard that I shouldn't bother going to Australia unless I planned to spend three weeks. I whole-heartedly agree.

Customs was dead easy. Nobody looked through my bags and I never had to wait in excessively long lines. On the way back, I had to claim my bags in San Francisco, go through customs, recheck my bags, and go back through security. However, I had a two-and-a-half hour layover, so it killed some time. If you're Oriental, plan on having your luggage checked. I didn't see one Oriental-looking person get to skip the bag checking line. Come to think of it, I didn't see any Indians get to skip it either.

Apparently I wasn't supposed to tip in Australia. I gave the bartender a 20% tip the first night and was treated like royalty the rest of the stay. I gave our waitress a 10% tip after she forgot to bring our dessert order and she wanted to give it back. I wish I lived in a country where there wasn't tipping, but it would take some adjustment.

I'm tired of talking about Australia. I'll post some photos on flickr later this week and hopefully make an Excel post or two. Oh, and the post title is a reference to the subtle language differences between Australia and the US. Conduct in Reserve was on a sign listing things you weren't supposed to do. One of those things was "drink intoxicating alcohol", as if there was some other kind. Also, "no worries" means "you're welcome", "take away" means you want your food to go, and "stop looking at my girlfriend", well that means pretty much the same in both countries.