Fresh From FriendFeed
RT @newscientist: We love graphene – and now the computer supermaterial could stop our shoes smelling. GREAT. http://bit.ly/aFDxiBThis guy on TV looks like Chewbacca.This is cool – Not only is @sprint offering Android 2.2 first, they’ll let you upgrade early. Contrast that to RIM/AT&T OS updates. 
Excel 2007: VLookup and Tables
I happen to think Excel got the most love out of the 2007 product line. My favorite (and most used) feature is the table. I wish they’d keep their formatting and relative size when pasting between office programs, but they’re still incredible.
If you want to learn more about table awesomeness in detail, you can check out the Excel 2007 table overview. In a nutshell, they allow you to specify a range of cells as a table, which can be formatted, grow, and be referenced by the table name instead of by inhuman nomenclature such as A2:C12. At this point, anyone starting to mention the benefits of named ranges needs to go click the link in paragraph 2, sentence 1. Named ranges are great, but tables take it to a whole different level.
As fantastically simple as it is to reference portions of tables both within and outside of a table utilizing “structured references”, it can be difficult to use the same structured reference within worksheet functions. Case in point: the VLookup function.
Allow me to waste another paragraph on setting up the background. I’ve created a sample table to help illustrate my situation.
In the sample table above I’ve got 4 columns. On another sheet, I’ve started to set up my reference to this table:
So far, so good: as long as new items get added to the table, the magical table will continue to expand downward to incorporate the new items – formulas and all.
But what happens when I add a new column to my original table?
Looks great, until I switch back to my Sold Items table:
All of my column references in the Sold Items table are off by one column. The problem is that when I added a new column to my original table, the Sold Items table kept the same column references. I need a way to make the number for the column change when columns are added or deleted.
How can this be done? Well, the obvious solution was to use the structured reference for the column. I can do this by simply typing in the name of the table, and Excel basically writes it for me:
However, VLookup only takes a number for the index attribute, and the structured reference in this case would be a range, not a number. So no dice.
I then tried making a VBA function to replace the standard VLookup. I found out that tables are referenced as ListObjects in VBA, but had some trouble using a Range to actually work with the ListObject (I’m sure it was something simple I overlooked).
Going back to basics, I looked at how the COLUMN function returns various columns within the table. I found it served my purpose well. Simply passing the table name returns the first column of the table, and passing it the column you wish to return gives you that column – all in numbers.
I added a + 1 to make the columns work out (5 – 3 = 2, but I want 3). The final VLookup is a little longer, but keeps the column references intact.
Messy, but it works!
Hopefully Excel 2010 will have another way to do this.
Back Home
I just arrived back home after a nice week on vacation. While on vacation I made some changes to my site that I hope will make it more useful. First of all, I installed Google Analytics. This helped me find out my most popular post, and also the trouble folks have been having getting around with my site reorganizations. I’ve also customized my 404 page to help out with any errors.
I’ve always intended my site to be representative of my interests (it is about me, after all). I’ve been using Twitter and Google Reader quite a bit, so I’ve included that in the new design. I’m also committing to at least one genuine (not posted from Twitter or Google Reader) post per month. I’ve done some more VBA work since I last posted about it, so I’ll probably put that up next.
Interested in any topics? Post in the comments. I’m not exactly expecting a lot of input here, but, if there is, I’ll do what I can to deliver 
Men are crazy. As a bonified member of the sex, I have the right to say this – along with every member of the opposite sex as well, of course. Sometimes, they’ll do some really extreme things to prove a point. Continuing with this line of thought, I’ve got a video here of a guy (Grahm Skee) who takes all sort of scary weapons after a few pretty sweet looking MP3 players. No scratches in the end, but don’t expect me to be taking knives and keys after my electronics anytime soon! No offense to Grahm, really; his post is pretty cool and has more video if you want to check it out. Oh, and his point? Buying a screen protector for an MP3 player is like paying extra for Monster cables, or paying Best Buy to “calibrate” your HD TV. Good to know!
I had to include the Zune:
Recent big news from the two big players in personal computing include updates to both operating systems and both player’s media devices. To no one’s surprise, the iPod update has had more attention, plus, the Zune team’s update, which was fairly big, was actually for their website. The entire podcast library for the Zune Marketplace is now available to stream directly from their website. While this new feature is cool, it’s one of those updates that no one really asked for…so maybe now that they got that bug out of their pants, they can actually make some updates to the player. To be fair, the team has actually said they’ll be rolling out new updates over the next nine months, beginning with some “little steps in March”. The Zune team, unlike the rest of Microsoft, really keep their cards close: most of the updates have zero feature leaks before they’re intended to be released.
Apple is also updating the iPod. They just released a new Shuffle, which doesn’t seem to be of great interest to anyone, except conspiracy freaks who are upset that you have to use Apple’s headphones. The real iPod/iPhone news is the 3.0 software update, which is rumored to have…wait for it…copy and paste! While I’m sure the implementation is going to be super cool (‘cause it’s Apple), it’s just ridiculous that the big news here is something that every other “smart” phone (and some not-so-smart ones, too) has had for the last 10 years. Still no video, though; which, even if I wanted an iPhone, would seal the coffin for me. I see no reason to pay hundreds of dollars for a phone which is lacks such basic functionality (even if the Japanese do like it). For an interesting side story, you can check out how to use VoIP to (temporarily) turn the iTouch into an iPhone. Apple is expected to preview the iPhone OS 3.0 features on St. Patrick’s Day (March 17).
I recently read an article by Joe Wilcox entitled “Operating Systems Don’t Matter”. It really stated the obvious (but overlooked) premise that people never buy a computer for the operating system, but for what they can do with the operating system. The hype of Windows versus Mac is just that to people who want to browse the web, organize photos, play games, or write an email. I think that Gates and Co really understood this back in the day. That’s why he was able to turn his entire company on a dime to provide the best office suite available, embrace the web as a part of the every day computing experience, and, as his last major initiative, take security as seriously as it should be taken. That last initiative (although it should have been done) probably cost Microsoft the adoption rate they would have had for Vista, because they stopped mid-stream to develop XP SP2. Long story short, Microsoft ended up releasing a system that most of the industry (and consumers) felt was severely under-developed. Apple had a field day with the debacle, and, at one point, actually included Vista in more advertising than Microsoft.
Fast-forward to today, and both Microsoft and Apple are again readying new releases for their operating systems. Apple isn’t saying much (as usual) about Snow Leopard, and Microsoft has released several versions of Windows 7 for the public to test. So far, the word is that MS has gotten their teams in shape and produced a quality bit of software. The new OS can run on the ultra-inexpensive netbooks with little performance degradation, improves on the fancy graphics that Vista introduced, and natively supports multi-touch (think all of the cool Surface features, but on your $400 netbook). There are also some new features to help you switch apps, which, as someone who usually has about 10-12 windows open at a time, I’m really looking forward to, and the ability to have “jump lists”: special shortcuts that jump directly to an action within an application, like opening an item from your history with the IE button. Perhaps the biggest news is that Windows 7 actually performs better than both Vista or XP in certain tests, which really improves the Vista bloat image. Still no word on WinFS, though (video).
While, as I mentioned, not much is known for sure about the Apple OS update, it seems to be their version of W7 – a largely incremental update that doesn’t aim to revolutionize the desktop experience, but slightly improve it. Some of the major updates include a much smaller OS (in size), and inclusion of Microsoft’s ActiveSync technology, which allows access to Exchange Server. They’ve also improved the Stacks feature, allowing you to click on a folder without actually collapsing the stack. Another feature that they’ve apparently had to spend a lot of time on is implementing AutoCorrect and AutoReplace and a feature similar to that in phones that identifies phone numbers, addresses, etc, and creates actions based on the type of information that it is. They’re also including a framework for developers to utilize the multi-touch capability of the touchpad in their applications. One fairly big item that I found interesting was the work Apple has done on the OpenCL technology. OpenCL basically lets programmers utilize the processors inside of a graphics card to do any number of actions without ever knowing the type of processor that is doing the work. The really amazing part is that OpenCL lets you do non-graphics work, like sorting text, or mathematical operations.
A professor of mine once made a bet with anyone who would take it that 10 years from then (2003) Microsoft and Apple would no longer exist as the major players that they were then. I took that bet, of course; and I’d take it again today. Both companies have introduced major new technologies that continue to make geeks everywhere drool and shell out money to buy their flashy new toys, and, eventually, function as practical tools for everyone else. And that’s not going to stop anytime soon.
Windows Mobile 6.5
Have you heard any of the news about WinMo 6.5? You can be pardoned if, at the very mention of Windows Mobile, you immediately started to dismiss this post - normally, I would have done the same. Click the picture on the left, though, and I think you’ll change your mind. Microsoft seems like they’ve finally woken up from their hibernation, and put out something worth checking out.
Now, I know that there are a lot of people who will immediately claim that Microsoft copied someone else when they did this, but really, every programmer out there copies someone else, so I won’t really entertain that argument. Face it, when some enterprising programmer builds a program that tweaks a product, and people buy it, what they REALLY wish is that the company that made that product would do that in the first place. The danger to that company comes when someone else comes up with a totally different product – like in the case of Firefox. I’ve talked about how I used Maxthon, and there were about a hundred other “browsers” out there that used the Trident engine, but added on extras. But when Firefox came out, that was (and remains) the real competition. Competition can be good though – IE7 is the best browser MS has released, and IE8 seems to be even better. In fact, going back to the copying argument, MS actually fostered the first development community for a browser with IE add-ons. Firefox just took it one better (and MS STILL hasn’t gotten that part right).
But the WinMo video illustrates exactly why I continue to root for MS. Microsoft is a massive company, and yet it can still move with lightening speed when it’s threatened. Think Zune is a joke? Think of this: In two years Microsoft became the second most sold HD MP3 player. Yes, I know that Apple out-sells them every year, but think about how many other MP3 players are out there that have been selling for years, and MS passed them all in only a few years. That’s really incredible, if you think about it. Are they going to beat Apple? Ha, I don’t think they will any time soon. But, if they were to focus a bit harder, who knows…Remember the XBox?
Until then, I’m looking forward to seeing what some real competition is going to provide consumers in the next few years. It’s an exciting time for a buyer’s market!
IE8 Mini Review
It’s been a while since I last posted, but I’ve been pretty busy. One of the things I recently did is install IE8 on my computer. I had been holding off on the beta, because I heard about a lot of crashes, but I was also really interested in some of the new features. When the recently announced the RC1 download, I decided to try it.
As everyone knows, after IE6 was released Microsoft sort of forgot about the browser market. Other companies started to develop features that were incredibly useful. The first browser I tried other than IE was Opera. I was on dial-up at the time and, to this day, I’ve yet to find a browser that loads pages faster than Opera (they have a great mobile browser, too). Opera wasn’t my cup of tea, though, so I quickly moved to Maxthon. Maxthon is still one of the best browsers out there, in my opinion. It has more options and features built into it than any browser on the market (before you add in plug-ins). The feature that quickly became a make-or-break feature for me was super drag and drop. This feature lets you just drag items on a web page, and they will open in a new tab, with the context of that tab depending on the item dragged. For instance, if I dragged a link, the new tab would open that web page. If I simply dragged text, that would open in my default search engine. If I dragged an image, that image would open up. It really suited the way I like to browse the web, and, for about 2 years, that was the only browser I used.
Then IE7 was released. Although it didn’t have some of the features (mostly drag and drop) that liked, I was able to use the IE7Pro add-on to replicate this, and some other features that Firefox has (like Inline Search). Until a few weeks ago, that’s been my main browser, although I did check out Chrome (nice attempt, but not good enough), and I use Firefox on the side sometimes.
So, as I said at the beginning, I just downloaded IE8 RC1, and I’ve run into only a few problems. It’s surprisingly stable, seems to be even faster than IE7, and adds some really cool features. First of all, the set of features I was looking for was detailed way back in the day by IE users. Although Microsoft learned their lesson with Vista, and didn’t promise everyone everything, I did think that at least half of those features would be in this release. Only some of them are in there, though. But the ones they implemented were done very well (as in not just copying Firefox), and there are a few surprises, too. They’ve finally implemented Inline Search, downloads are scanned for safety, tabbed browsing has colors now to group them with the parent tab they originated from (and you can close entire groups), there’s a redesigned New Tab page, session saving (and recovery), WAY better zoom function (zooms the whole page), the back button works like you think it should for “rich” applications now. They’ve also introduced new threads for each tab, which is nice from a stability standpoint, but it’s impossible to know which process goes with which thread in task manager. There is a new “add to favorites” button that only requires one click (and there’s a cool animation when it’s added to your favorites bar), and you can add RSS feeds to the Favorites Bar. You can also search your history now. While you can see the whole list here, below are two features that I have really enjoyed. Add any other features you like in the comments.
Search
- Search Suggestions/Answers: Firefox’s search box has always been better because they offer search suggestions (not just history). IE8 one-ups Firefox, though, by integrating their Answers service into the search box. Type in “weather”, and you get your local weather (I’m not sure how they determine where you are, though). Type in “2+2” (or any other math problem you have trouble with), and you get 2+2=5. Type in “Define download”, and you get the definition for download. Pretty freaking cool. Oh, and if you happen to be searching Amazon, your “answers” come in the form of thumbnails, so you can preview each item to choose the exact one that you like.
- Choosing your search engine: It’s even easier to switch which search engine you’re using for individual searches on-the-fly. At the bottom of your search box is a list of each engine you’ve added, and you can switch between them by just clicking the one you want. When you switch, the search suggestions are updated for that search engine, so you can continue to customize your search per engine.
Address Bar
- New? keyboard shortcut now: Shift-Enter accepts the top suggestion for AutoComplete. They’re also showing the keyboard shortcuts along with the actions now, which is a great reminder.
- AutoComplete: Although they’ve gotten rid of inline AutoComplete, the new shortcut is growing on me, and I think I like it. It also shows your history, but it’s split now: on the left is the URL, and on the right is the website title. It makes it easier to find that website that you’re looking for by the name, not just the URL.
Have you used the new build? Let me know what you think!
Claiborne Pell died yesterday
You may not know who Claiborne Pell is right away, until you remember the Pell Grant. Passed in 1965, the Pell Grant has helped over 54 million Americans attend college. He was also part of the conference that drafted the founding charter of the UN, was the main sponsor behind the creation of the National Endowment for the Arts. Even though he was a multimillionaire, Pell ranked the Pell Grant program as his single greatest achievement.
Some things about Pell I found really interesting:
- Pell’s definition of his Senate job: Translate ideas into actions and help people.
- Quote: “I always try to let the other fellow have my way.”
- From Projo.com: “He was such a terrible driver that he drove for years in a white Mustang that was fitted with a roll-bar. That feature — plus the array of body dents and the pelican hood ornament he had borrowed from his family crest — always distinguished Pell’s car from the somber sedans at the foot of the Capitol steps.”
- Pell’s rules of campaigning: Don’t attack the other fellow. Keep a sense of humor. Do the unexpected.
In other news, a “biennial study by the National Center for Public Policy and Higher Education, which evaluates how well higher education is serving the public, handed out F’s for affordability to 49 states, up from 43 two years ago. Only California received a passing grade in the category, a C, thanks to its relatively inexpensive community colleges.” “Only two states — New York and Tennessee — have made even minimal improvements since 2000, but they’re still considered to be failing. Everywhere else, families must fork over a greater percentage of their income to pay for college. In Illinois, the average cost of attending a public four-year college has jumped from 19 percent of a family’s income in 1999-2000 to 35 percent in 2007-2008, and in Pennsylvania, from 29 percent to 41 percent.”
Rainmeter skins
I frequent Lifehacker – a fairly useful blog on making it easier to get things done. Not too long ago, they featured a Rainmeter skin called “10-Foot Tall HUD”, which I found pretty useful. Over my break I adjusted and tweaked the time and date skin, and also combined the icons from this guy’s set to tweak the weather version. I resized the icons to be half as small, but otherwise I’ve just borrowed from both guys and rearranged a bit.
Here’s the weather skin, in both dark and light. They’re transparent, so the blue and white is just my background:
And here’s the time and date skin, again, in both dark and light versions:
Clicking on the pictures should get the files. If you’re not familiar with installing Rainmeter skins, you simply need to extract the folders in the zips into the skins folder. I don’t believe these will work with Rainlender – the updated version of the widget program – I haven’t quite figured that one out yet. I’ll probably focus on learning Yahoo’s widget program instead, since it seems like they’ve really done good stuff to it since the old Konfabulator days. Any feedback you may have would be appreciated!
I had a small dilemma, and I couldn’t find the answer on Google too readily, so I’m sharing my solution here. I had a list of names that were in the order First Last, like this:
| John Doe |
| Jane Smith |
I needed them in Last, First order. Here’s the Excel formula I used:
=TRIM(RIGHT(A2,LEN(A2)-SEARCH(" ",A2))) & ", " & TRIM(LEFT(A2,SEARCH(" ",A2)))
This gave me:
| Doe, John |
| Smith, Jane |
It’s not terribly difficult, but maybe it will help someone!
