Useless Blogging

August 11, 2010

Awesome Excel Trick for Named Ranges

Filed under: Programming,Work — steegness @ 4:04 pm

I had been using the OFFSET() function with named ranges to create ranges with dynamic numbers of rows for a while, which worked very well, except when the source data was updated by moving columns around.  If a column was added or deleted, I needed to update almost every defined name I had.

To counter that, I made a formula for a named range that only cares about the column header:
=OFFSET(INDIRECT(“Source!$” & CHAR(64+MATCH(“ColumnName”,Source!$1:$1, 0)) & “$2”), 0, 0, COUNTA(Source!$A:$A)-1,1)

Now, I can refer to this named range and get the right column no matter where it is on the Source tab!  Tada!

(Yes, I know.  Sue me.)

3 Responses to “Awesome Excel Trick for Named Ranges”

  1. Jason Says:

    So, this came up while I was away, but it means a potential opening for me. My new job requires a surprising amount of spreadsheet work, and while I’m a fairly decent user, I’m not up on all the tricks with Excel (not having owned a copy in years). Any recommendations on decent books/online tuts for making the jump to Power User, as it were?

  2. steegness Says:

    Most everything I know about abusing my powers in Excel I learned initially from John Walkenbach’s books. http://j-walk.com/

    Online, I’ve been really digging http://www.chandoo.org/wp/ lately. It’s not a here’s-how-you-take-over-the-world-with-Excel site, but it does showcase a lot of useful tricks and novel ways to get what you want out of Excel.

    Personally, I say if you learn VLOOKUP() and SUMPRODUCT(), you’re 90% of the way to taking over the world.

  3. Jason Says:

    I need more time to grok VLOOKUP() in its fullness and thus begin to see its true power. SUMPRODUCT(), though, fell into my hand like the bone in “The Dawn of Man” from 2001 – and my poor computer’s processor was the skeleton.

    However, I quickly learned not to insert 1500 SUMPRODUCT(X:X) calls into a 750-column spreadsheet as it’s being populated, and that appears to have calmed things down to accomplish exactly what I needed to do today.

    So, hey, if nothing else, thanks for turning me on to SUMPRODUCT(). It already saved me a ton of work on day one.