Category Archives: Summit 2009

Dealing with VLOOKUP and GETPIVOTDATA errors

Posted on by
11

I need to thank Joe Chirilov and Danny Khen, from the Excel team at Microsoft for this tip. We were discussing some Excel things while we were having dinner during the summit, and I was bemoaning the fact that it would be really nice to have an additional argument for the VLOOKUP function that returned a value instead of #N/A if nothing was found in the list.

The method I had been using was the tried, tested and true approach. Here's what my VLOOKUP would look like:

=IF(ISNA(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0, VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False))

Now, it's no secret that VLOOKUP is a pretty heavy formula. If you don't believe that, stick a thousand in a worksheet and see how much it starts to slow down. If you count the function calls in the above, you'll see that instead of calling VLOOKUP once, I had to call it twice, as well as calling both the IF and ISNA functions as well. Four functions to return one result. And while I haven't formally benchmarked the speed, I can tell you that my workbooks were running very slowly.

Both Joe & Danny looked at me kind of funny and asked why I wasn't using the IFERROR formula. My response was something really clever, like "Huh?"

I'm really glad that we had the conversation at all. This program is so big, that things just slip in during releases that we either don't hear about, or I don't notice. 🙂

The IFERROR approach to this issue is far superior to the old method, in my opinion. Using IFERROR, the same VLOOKUP results can be achieved with:

=IFERROR(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0)

Much better!

What really spurred the discussion was that I'd also been working on (and still am) a set of financial statements that are driven by pivot tables. In making heavy use of the GETPIVOTDATA function, I was running into the same problems there: If an account/department combination didn't exist for the month, I would get a #REF! error. To solve this, I'd wrap them up in an IF/ISERR combination like this:

=IF(ISERR(GETPIVOTDATA("Amount",PT_Actual_YTD!$A$5,"GLMonth",rngMonthEnd,"Account",$D102,"Dept",$E102)),0,GETPIVOTDATA("Amount",PT_Actual_YTD!$A$5,"GLMonth",rngMonthEnd,"Account",$D102,"Dept",$E102))

With IFERROR, the formula compresses down to:

=IFERROR(GETPIVOTDATA("Amount",PT_Actual_YTD!$A$5,"GLMonth",rngMonthEnd,"Account",D46,"Dept",E46),0)

In my opinion, IFERROR is a far superior approach than the prior. It is so much easier to:

  • Write
  • Read
  • Maintain

So far as I can see, it pretty much wins on all counts but two…

  • This function is only available in Excel 2007+. Open the file in a previous version of Excel and all those awesome formulas return #NAME? errors. 🙁
  • It is not generic enough to capture the difference between #N/A, #REF! and other errors. While this isn't an issue for me, I'm sure someone will have a practical use why they might want to react differently if one type of error was returned vs another.

At any rate, as great as this function is, and as much time as it will save me in the long run, it didn't go in without issue for me. Naturally, all of my financial information also has to be sent to head office. And what do they use? Why Excel 2003, of course! In some ways I'm kind of glad that I didn't put that together immediately, so I had half my file converted to IFERROR before I realized the issue. Had I not done so, I might have stuck with the slower approach, even though I need the time most at month end. Instead I wrote a utility to copy the sheets they need to a new workbook and break all the links back to the original source. A little bit painful to have to do, but at least I can still take advantage of the new approach, and they can have files without #NAME? references in them. 🙂

MVP Summit Wednesday

Posted on by
4

And so, sadly, Wednesday dawned as the last day of the 2009 summit. Breakfast was a huge buffet at the WTCC:

This is where we found Richard again, looking a little worse for wear. Reminds me of the first morning of my first summit. <grin>

It ended off with a half day of keynote speeches, and then lunch. They keynotes were interesting, with Steve Ballmer's being as energetic as usual. He really is a dynamic guy, and I believe genuinely values the contributions that the MVP's make to Microsoft's products. One memory I'll take away is him signing the back of a photo. This particular photo was of an MVP (Frank) who had passed away shortly before the summit. The Small Business Server guys asked Steve if he'd sign something for Frank's family. Steve called him up and proceeded to write on the back for about 3 minutes. Not just a line or two, but a real essay. He didn't have to go to that length, and it really displays something of his character, and of his feelings of the MVP crowd.

After the keynotes, we broke out and there were a lot of goodbyes as people had to start heading off to flights. We did manage to get a few photos along the way though:

That's Kevin Jones on the left, Mike Rosenblum, me in the hockey jersey, Richard Schollar in the back row beside Greg Truby. Smitty Smith is in front of Richard, and Tom Urtis is beside him. Below is the photo of the Canadian MVP's in the our hockey jerseys… a tradition at the summit keynote. I'm in the back row somewhere.

From there, we headed back to the Westin again, and then Dee, Mike Rosenblum and I went out to dinner at Il Fornaio. It was a great meal with some awesome in depth conversation about .NET code. (My wife is just awesome about me getting into geek talk with someone!). Mike is a great guy, and we both had a lot of fun over dinner.

And then, to close off the event, the three of us headed back to the Westin bar where we met up with Roger Govier and a couple of UK MVP's that he brought with him. Nate Oliver also dropped in too. One of the UK MVP's that Roger brought was Joe Fawcett, an XML MVP whom I'd met at a previous summit. Seeing Joe caused another round of geek talk to break out as the whole group got into a deep discussion on the compatibility of XML Namespaces. (Look, snicker all you want, but it WAS a technical event!)

I really had a blast this summit. Learned a lot, gave a lot of feedback and met some great new people. Now I just have to make sure I get invited back next year. 😉

MVP Summit Tuesday

Posted on by
2

Again, another early rise to breakfast, and off to the bus again. Fortunately the session started late due to technical difficulties as the bus got a bit snarled in traffic!

That morning we actually saw something very, very cool which I can talk about since a public post has already been released. It is called Gemini, and it's a pretty slick data mining tool for Excel. Basically what it allows you to do is connect Gemini to database tables. It instantly analyzes the tables and sets up relationships. You can even add new tables of external data, and it will analyze those and add any relationships it finds. From there, and this is the really cool part, it sets up the OLAP cube functionality. So in Excel 2007 and beyond, you can use Excel's built in cube functions to pull your data out of the cube. To the lay person what this means is that you can link to your General Ledger table and then write a formula that pulls out GLBalance ("Account","Department","Month"). Over the last year I spent a ton of time programming that exact functionality, and soon I'll be able to do it instantly. Seriously, they demoed analyzing 110 million rows in a couple of seconds. It was a real WOW moment.

The really awesome thing here is that it puts the OLAP cube functionality into the hands of the BI professional, not the IT department. You'll no longer need to install a SQL server, install Analysis Services, create dimensions for the cube and anything else you need to do that I don't know about. Very, very cool. I can't wait to get my hands on a Beta of Gemini.

The rest of the sessions were great too, but due to my NDA, I can't talk about them. Sorry!

After the sessions, we headed back to the hotel, then off to the Music Experience Project for the big MVP party. Lots of food, beer & wine to be had for sure. (There were several of the buffets like the one below)

In addition, there was Rock-a-roke. Kind of like karaoke, but with a live band. A live band and a lot of MVP's whose talents lie in helping people… not necessarily signing. (Kudos to them for getting up and entertaining though!) Here's a picture of my MVP lead Sasha Krsmanovic "singing" Hit Me Baby One More Time. 😀

Eventually they closed down the bar there, so we had to go find another one. With Mike Alexander absent, no one felt it necessary to wander into a seedy bar, so we headed back to the Westin lobby bar again… as did every other MVP I think. Seriously, I've never seen so many people in there before. I found out the next evening that we had run them out of one of their beers!

Dee finally made it out with us on Tuesday night. That's Greg Truby on the left, Zack Barresse in the middle and Dee on the right.

We had a ton of fun that night, as you can see in the picture below…

Smitty, Richard and Zack are enjoying is Roger's antics... here he is dancing around in a woman's jacket!

Somewhere during these antics Richard disappeared. We wondered aloud where he went, and then again half an hour later. The only trace of his being there was his full glass of red wine looking lonely on the table. Bob Umlas has a picture of him looking particularly ill the next morning. 😀

It was a late night again on Tuesday. I think another 2AM turn in again. Thank god breakfast wasn't until 7:30 on Wednesday!

MVP Summit Monday

Posted on by
Reply

It always irks me when I look back and realize that I didn't take many pictures…

Monday dawned way too early. I made it down for breakfast, and then off on the 20 minute bus ride to the Microsoft Conference Center on the Redmond campus. I should really take a picture of that building every year. I'm just really glad that the morning session wasn't too intense. 🙂

At any rate, from there it was off to our first real sessions with the Excel team, which was great. I genuinely enjoyed all the sessions this year. After the sessions were over, Zack, Smitty and I headed off to the Microsoft company store, (which has moved since last year and looks great.) And from there it was back to the session building for dinner with the Excel Product Group.

Dinner with the product group is always pretty cool. This year I got the chance to spend some time chatting with Joe Chirilov and Danny Khen, discussing everything from IFERROR formulas (awesome formula!) to Pivot Tables to AutoFilter/ListObject Filter differences. Some serious geek-talk that was a hoot!

So the only pictures I have of Monday are below. This is Zack Barresse (on the sign), and me lounging against it.

It's kind of neat too that every lamp standard on the Redmond campus bears one of these banners. They sure do make you feel welcome as an MVP.

After the Product Group dinner, we headed back to the hotel. We set up in the Westin lobby bar that night, and ended up heading to our rooms before the bar closed. If I recall correctly, I got in around 1:15AM that night… and set the alarm for 6:00AM again.

MVP Summit Sunday

Posted on by
Reply

Okay, finally I'm going to put up some pictures here. I'll break them up a bit so that the load time isn't too long. First off, merely for my interest, here's the view from the hotel room:

Now, of more interest…

Dee and I did start the day off with breakfast at Lowell's, after which we headed to the WTCC so that I could register. My first impression was that this year MS had scaled way back. In past years you went up the main escalators to the top floor and all the registration was right there. This year I did that (missed a sign somewhere) and had to go through the back hallways to find the registration desk. Felt like we were tucked away in a closet. It turns out that I missed the escalators that go to the right place although it still felt a bit like it was in the back room.

Dee and I went shopping for a while, then I spent a while chasing around the WTCC looking for Roger Govier whom, based on the Twitter posts, I kept missing by a few minutes. I returned to the WTCC for the keynotes and met up with some folks though, and here's some pictures.

Bob Umlas at the welcome reception. Bob, incidentally, did not take a vow of silence.

Unfortunately, Dee fell earlier in the day, so was feeling a little too sore to make it out to the Rock Bottom. I headed out and had a few beers with the crew below. From left to right: Tom Urtis, Roger Govier, Greg Truby (you can only see his head here,) Tushar Mehta, Bill Manville, Bill Jelen, Richard Schollar (looking the other way,) Mike Rosenblum and Nate Oliver.

From the other end of the table, again from left: Bill Jelen, Richard Schollar, Mike Rosenblum, Nate Oliver, Kevin Jones, Tom Urtis, Roger Govier and Greg Truby.

And another shot that has a couple of others in it as well. From left to right, front row: Zack "No really! I was at last year's summit even though no one saw me" Barresse, Greg Truby, Tom Urtis, Curt Frye. Second row: Bill Jelen, Mike Rosenblum, Roger Govier, Richard Schollar and myself. Back row is Nate Oliver and Smitty Smith.

Eventually we got kicked out of the Rock Bottom, as it closed, so we started to head back to the Westin. On the way back to the hotel we somehow lost almost the entire group! Curt and I closed down the Westin bar, with the rest of the group showing up after a while. (Apparently they stepped into the Elephant & Castle on the way.) I think I rolled into my hotel room at about 2:15AM, setting the alarm for 6:15…

Summit pics are coming…

Posted on by
Reply

I promise... they're coming.  I've eaten up the last couple of evenings working on something important, but hope to post pics within the next couple of days.

One a cool note, we finally got our new CRM software working yesterday at work.  It's a product called Lasso, and is a web based system to track customer interests, contact information and even real estate closings.
We've had the system for a couple of years now, but haven't really done much with it beyond hand post contact information into it.  We sent out our first emails yesterday to people who attended a trade show, and it worked like a charm.  Our hope is that we'll be able to use it to drive business our way, and we could sure use the help.  (Everyone could in this economy, couldn't they?)

There's still work to be done, such as linking the database to our website.  We'd like people to be able to sign up online without having to go through a human to do so.  In addition, we'd like them to be able to manage their own profile, logging in to update contact info and preferences, such as what types of things they'd like to hear from us about.  Our eventual goal is to use this to markte to people based on the things they'd like to hear about.  (Golf lessons, tournaments, weddings, lunch specials, real estate offerings, etc...)

We'll see how it all goes, but it's been a really big win for us.

World’s Smallest Fridge

Posted on by
3

Every year when we come to the Summit, we always request a fridge for the room, so Dee can buy some fresh fruit from Pike Place Market. Normally we get a standard mini-fridge, but this year they were all out of those apparently… They did have a mini-fridge though. Just to put it in size perspective, I put my RibbonX book next to it:

Now granted, it's a big book, but not that big!

In Seattle

Posted on by
1

Dee and I are currently in Seattle, as I'm attending the MVP summit for the 3rd year in a row. We're now happily checked in to the Westin, and waiting for registration to start tomorrow.

We came down on the Victoria Clipper ferry again, which is a fairly nice way to travel. The only beef I have with it is that, at this time of year, there is only one trip to and one trip from Seattle per day. So we had to catch the 5:00PM boat down, which means it's about 9:30PM before we're in our room. I can't really eat before we go, as we have to be there at 4:00, so that means you'd have to hit somewhere around 3:00 to actually get a meal, and I'm just not hungry at that point. Sure, you can buy food on the boat, but it's pre-packaged snack type food, and is far from a satisfying meal. I'd way prefer it if they sailed from Victoria to Seattle at, say 2:00. You'd get in so much earlier and would actually have time to do something in the evening.

Oh well. Regardless, it sure beats the bus, is a pretty easy way to travel with some nice views. Despite the fact that we went through a nasty rain shower, the ride was mostly smooth, and we're here now, so no more complaining.

I just got back from a good dinner at Gorden Biersch. The last time I was there was 11 days shy of two years ago. I only know this because I blogged about it. 🙂 That was the first night I'd ever met any of my MVP colleagues in person… very hard to believe that it's been 2 years since that time.

Tomorrow morning we're going to start off with the traditional breakfast at Lowell's, and after some time at Pike Place I'll head off to registration to see who else is here.

 

Twitter

Posted on by
3

I figured that I'd give this a shot, so we'll see where it leads.  I've set up a Twitter profile, and linked to it on right side of my main site (just under the RibbonX book on the right).

I'll have to be careful what I say during the summit, since I did sign an Non Disclosure Agreement with Microsoft, but I should be able to provide some non-confidential updates.  Weird thing though... within 32 minutes of signing up for an account, and before posting this, I already had my first follower.  That's just weird!

At any rate, I haven't spent more than 20 minutes with this, so if you want the RSS feed for it, it's here.  I'll try and figure out if I can stream it into the blog at a later date if I'm taken with it.

Right now, I can only wonder how much this is going to cost me, as I have to send updates via SMS (text message) from my phone... and I don't have a plan.  That may have to change!  🙂