Excel

Six handy Microsoft Excel shortcuts to make your life a little less painful

The Poorhouse spends a lot of time looking at dull grey grids of numbers. It's not a hobby per se, but it happens. As per pretty much any other normal business, these numbers appear a lot in Microsoft Excel where hours upon hours of top fun can be had moving them around a bit until they sort of hint at some sort of conclusion that makes you look good. But between staring at these dire digits comes time to prepare for staring at dire digits, which leads to magical shortcuts being discovered.

An easy way to make the content of one combobox in Excel dependent on that of another

Comboboxes, aka dropdown boxes, are useful tools for constructing e-forms, restricting idiot-user responses to limited-choice fields and soon. Microsoft Excel has many a way of allowing you to create these, whether this be via the Forms toolbar, Control Toolbox toolbar or the Data -> Validation menu option.

One especially useful feature of such choice-enabling controls is that of making the choices in one box dependent on what the user chose in another. For instance, if a user chose "animals" in box 1, the other could allow "fox", "badger" and "pig" as options, but if they chose "vegetables" in box 1, the other box could only allow "carrots", "cauliflowers" and "cabbages" as choices.

Online file conversion

The Internet is great for sending files around, whether they be documents, pictures, sounds, videos or any other such electro-data. What is sometimes less great is when the proud recipient of your favourite picture of your hilarious office antics, or chain letter that if you don't forward to 7 people immediately you will die a painful death, can’t actually see the file because they don't have the right obscure program needed to open it.

This problem is only exacerbated by the profusion of admittedly cool new technologies where for instance a Nokia phone can bluetooth a sound recording to your Apple Mac via a Palm PC and so on. Who knows what format that will turn up in, or how to open it? If horror-of-horrors you aren't even on your own computer the chances of you finding and installing a program that will let you convert such things is minimal. So luckily you don't have to any more, Media Convert will do it for you via the web.

Counting rows using multiple criteria in Excel

Often in Excel you have a big fat table of data that you need to perform various analyses with. It wouldn't be out of the bounds of reasonability that you would want to count the number of rows that have a certain set criteria. Luckily, Excel provides the COUNTIF function to do exactly that. But it has severe limitations.

Deal or No Deal - Microsoft Excel edition

Countdown beware; there's a newish favourite daytime gameshow in town to take the shallow attention of you non nine-to-fivers: Deal or No Deal. If nothing else, at least it keeps Noel Edmonds of the cruel streets of England.

In case you've been asleep for years (being in foreign parts is no excuse - there are at least 23 national versions of the show). The premise of the game is as follows: There are a number of boxes with varying amounts of money in, from 1p to £250,000. The contestant picks one box, and it is opened to reveal what it contained, hence giving the contestant the knowledge of what is left to play for. At some point in the game, a mysterious "banker" rings up and offers the contestant a specific amount of money if they will take it and leave.

Mission (im)possible: Calculating the difference between two dates in Microsoft Excel

It came upon the midnight clear
That glorious song of old
I want to calculate the difference between 2 dates in Excel
Or be left out in the cold.

Yes, the Poorhouse was confronted with the above situation; namely that given a date1 and a date2 in Microsoft Excel we needed to know the difference. Not the difference in terms of random Excel microseconds since 2nd August 1957 type numbers, but an actual, readable "3 years 2 months" type difference.

Perhaps this doesn’t seem like quite the craziest of plans; surely people are forever wanting to calculate someone's age, length of service and so on? But as far as Mr Excel is concerned it is a task as arcane and bewildering as the search for the Holy Grail.

When docmd.transferspreadsheet commands go wrong...

Slightly lazy VBA programmers might enjoy using the less-than-cryptically-named docmd.transferspreadsheet command when trying to automatically move information from an Access database to a spreadsheet or vice versa. It allows you to work with spreadsheet ranges and so on with minimum faffing. It also doesn't work so great and can lead to "unexpected results" when importing from a spreadsheet.

Syndicate content