Skip to content

Creating Dynamic Named Ranges in Excel® using OFFSET & COUNTA

Following on from Creating Named Ranges in Excel®, this blog post describes how to create dynamic named ranges.

If you’re not familar with named ranges, I’d really suggest google searching “named ranges in excel” or read the post linked to above.

As mentioned many times on this site, named ranges are really useful, however there are situations where the data you need to reference is not in a static cell range – that is, the cell range is dynamic. To combat this, we can use the OFFSET and COUNTA functions.

First, let’s look at COUNTA – it is a function that most Excel users would be familiar with. The COUNT function returns the number of numbers in a cell range. For instance:

1 to 10, a to j

1 to 10, a to j

Continue reading ›

  • Share/Bookmark
Tagged , , ,
2010 05 04 Ryan Kirgan Excel Tips Comments (2) Permalink

Creating Named Ranges in Excel®

Named ranges are great. They allow the user to define a cell range with a single text-descriptor. For example, you could define the cell range of $A$1:$G$1 as the string “headings” and every time you wanted to refer to that cell range (in, say, a formula) you use the string “headings” instead.

I’ve alluded to them in Matching data in a table in Excel® using INDEX and MATCH (- a VLOOKUP alternative) and find named ranges invaluable for a number of reasons:

  1. With appropriate “names” they can help explain what the range describes.
  2. They (usu.) reduce the size of the formula(s).
  3. They simplify reuse of the range.
  4. By keeping the named range in a single location, if the range needs to be updated, it only needs to be changed once.
  5. They reduce the chance of typographical errors when, particularly when used more than once.

Continue reading ›

  • Share/Bookmark
Tagged ,
2010 05 04 Ryan Kirgan Excel Tips Comments (1) Permalink

Increasing your virtual memory (paging file) with Windows XP

In many operating systems you can use free disk space (which is typically in a abundance) to emulate RAM. It’s obviously not as beneficial as physically adding more RAM to your machine, however here is a brief guide to customising your virtual memory configuration using Windows XP.

- Press the Windows Key + Pause|Break key to open the System Properties windows (there are many ways to do this!)

Continue reading ›

  • Share/Bookmark
Tagged , ,
2009 04 14 Ryan Kirgan Windows XP Comments (0) Permalink

Viigo has installed itself! Or did it? Optus are at it again.

Looking at the Application icons on my Blackberry I’ve noticed something that wasn’t there before: Viigo RSS Reader. Having no idea what this was (and knowing that I hadn’t installed it myself) I did a quick google search to discover this is an extremely popular RSS reader (as the name suggests) for Blackberry devices.

I didn’t ask for it, so no matter how good it is isn’t really the point. The problem quickly became that I couldn’t seem to uninstall it! Options->Advanced Options->Applications didn’t contain anything that even slightly resembled Viigo RSS Reader. Similarly, pressing the Menu Key and selecting Modules in this section does not show anything.

I then delved into the Service Book (also found in Options->Advanced Options) to find that Viigo VPL [Browser Config] was present and active. Viewing the properties of this service book showed:

Description: Viigo Virtual preload for Optus AU

Pressing the Menu Key and Delete seems to have solved the problem, but with carriers such as Optus being able to deploy potentially unwanted software, who knows for how long it will remain off my phone.
Continue reading ›

  • Share/Bookmark
Tagged , , ,
2009 03 12 Ryan Kirgan Blackberry Comments (0) Permalink

A comprehensive how-to set up a wireless network (securely) with Windows XP guide

I wrote this how-to guide a while back, however thought I would publish it on my blog in response to: Stop Internet Poachers from Stealing Your Wi-Fi by Rick Broida. My advice to people is diametrically opposed to Rick’s, so hopefully between the two there’s a solution that best meets your requirements :)

Disclaimer: This is how I set up wireless networks, however I am sure that there are a lot of different ways to do this. Apart from any references to upgrading firmware (in short, don’t do it unless you have to!) there shouldn’t be anything in here that breaks your equipment and ends up costing you lots of money!

In the examples provided I have used:

- Intel(®) PRO/Wireless 3945ABG Network Connection wireless adapter
- Linksys WRT54G wireless router

This document is aimed at enabling secure, wireless access to a wired network that is already operational. While setting up a home network from scratch is beyond the scope of this document, I will say briefly that there are two main ways (that I know of) to get things working: either the modem does the authentication/DHCP etc. or the router does (and the modem is set to “full bridge” mode.) The following instructions should work irrespective of the method adopted.
Continue reading ›

  • Share/Bookmark
Tagged , , ,
2009 02 26 Ryan Kirgan Wireless Networking Comments (0) Permalink

Google Earth Update Review [v5.0.11337.1968 (beta)]

The other day I updated my Google Earth installation to 5.0.11337.1968 (beta) and was happy to see some new functionality:

Flight Simulator

Fly a F16 (a.k.a. “Viper”) or old-school SR22 over your house. As far as I know, this function was previously available to Google Earth Pro users only. Not advisable for those who already have a fear of flying as you realise just how hard it is to fly a plane :)

You can do this by clicking on Tools -> Enter Flight Simulator…
Continue reading ›

  • Share/Bookmark
Tagged ,
2009 02 17 Ryan Kirgan Geocoding Comments (0) Permalink

The weighing twelve (12) balls riddle – which one is a different weight?

This is a riddle that a friend (Carl “Chunky” Benson) put to me a few years back. A few A4 pages of scribblings and I had accounted for an “answer” for almost all possible outcomes (almost), however, I never arrived at a complete solution.

There’s every chance that I’m just hopeless at Maths, so prove me wrong people :)

Premise

  1. You have twelve (12) balls and a set of balance scales.
  2. One (1) of the balls is a different weight to the other eleven (11) balls.
  3. You are allowed to use the balance scales three (3) times.
  4. You need to determine which ball is the “odd one out” and whether it is heavier or lighter than the other balls.

Now these are balance scales I’m talking about. While a solution involving electronic scales would be effective, I don’t think it’s what the riddle’s architect had in mind ;)
Continue reading ›

  • Share/Bookmark
Tagged
2009 02 13 Ryan Kirgan Mathematics Comments (6) Permalink

Matching data in a table in Excel® using INDEX and MATCH (- a VLOOKUP alternative)

This article describes an extremely basic Excel® function (well, two functions actually) and is more a test of my new WordPress installation than anything else :)

It describes how you can use the INDEX() and MATCH() functions to look up data in a matrix.

Background to VLOOKUP()

Many users are familar with the VLOOKUP() (vertical lookup) function that simply looks up a value in the leading column of a table and returns the value in another specified column of the same table. The required parameters are:

VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup) where Range_lookup is optional.

For example:

With the data in the table below…

A B
1 Fruit Colour
2 Apple Red
3 Banana Yellow
4 Orange Orange (what else!)

…the formula

=VLOOKUP(“Banana”,$A$2:$B$4,2)

Result: Yellow

will search for “Banana” (1st parameter) in the leftmost column of the table ($A$2:$B$4) (2nd parameter) and return the value that is in the 2nd (3rd-parameter) column.

It should be noted that the 1st parameter (the value of which, here, is “Banana“) can also be a cell reference (e.g. $D$1) rather than statically entered into the result cell formula.
Continue reading ›

  • Share/Bookmark
Tagged
2009 02 02 Ryan Kirgan Excel Tips Comments (5) Permalink