Useless Blogging

Awesomer Excel Trick

steegness — Wed, 01 Sep 2010 13:47:18 +0000

OK, so the formula that I last posted apparently required a little work. It became immediately obvious that the use of the formula for a named range failed if the matching column was to the right of column Z; ther CHAR() function will only return one character. I needed to make the formula a bit more complex in order to handle that.

=OFFSET(INDIRECT(“Source!$” & IF(MATCH(“MatchingColumn”,Source!$1:$1, 0)>26, CHAR(64+TRUNC(MATCH(“MatchingColumn”,Source!$1:$1, 0)/26)) & CHAR(64+MOD(MATCH(“MatchingColumn”,Source!$1:$1, 0), 26)), CHAR(64+MATCH(“MatchingColumn”,Source!$1:$1, 0))) & “$2″), 0, 0, COUNTA(Source!$A:$A)-1,1)

Go to town!

As seen at Royal Farms

steegness — Tue, 17 Aug 2010 01:35:28 +0000

The MAN!

Awesome Excel Trick for Named Ranges

steegness — Wed, 11 Aug 2010 20:04:23 +0000

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.)

Downtime?

steegness — Sat, 07 Aug 2010 01:27:02 +0000

I find myself in Allentown once more. It is the second time in just over a month.

This past week, Emma was out at a camp, so Julie took the time of half-the-kids and went a little crazy with the paint. I joined in the fun as well, and now Sara has a fresh bedroom (pink/yellow), Emma has a fresh bathroom (blue) and we all can share a fresh living room (brown).

Today I took off from work so we could trek up here to Allentown early: this allowed Julie to go out with her father and brother to the DCI’s, a national drum corp competition. Sara, Sighgus, and I are back at the house. Thus far we’ve watched some TV, played some DS games, and taught ourselves to play Frere Jacques on the piano (I CAN STILL READ MUSIC… STUNNER!). Not a bad night.

Tomorrow, we watch Emma’s big show and take her home. No idea what next week will bring with it. With luck, a nap.

Focus!

steegness — Sun, 11 Jul 2010 19:46:01 +0000

I seem to be getting to a point where I don’t think I’ve been since college. Three nights a week I’m out and about, I’ve got beaucoup work responsibilities, and I’m PTA president. Man, I’m busy.

It even seems like my downtime needs to be scheduled at this point. It’s a weird place to be. Not fully certain I like it here.

My boss told me to change the sign

steegness — Mon, 12 Apr 2010 02:35:19 +0000

Another enrollment season is upon me at work; once again, we opened our doors to the students of the Year Not Yet, and with minor exceptions, things went off exceedingly well. We’ve still five more programs to open — two tomorrow — and one more that is coming “at some point”, but we’re out of the thick of things.

I’m trying to progress as a boss. That’s probably where I should stop; I can’t think of anything to say (positive or otherwise) that won’t potentially land me in hot water. Just know that I’m trying to do better.

Home life has been interesting since the Bad Weekend below. Many things have turned around, and I’m finding myself having a renewed sense of purpose thanks to my family (even if work has precluded me from seeing them a whole lot lately). I love my wife and kids, and am extremely grateful for the strength they offer me day in and day out.

There’s more on my mind, but it’s got a different theme, so more to come.

A bad weekend.

steegness — Mon, 08 Feb 2010 18:50:55 +0000

For the record, being stuck inside my house this weekend has been hell.

An always impressive week

steegness — Thu, 04 Feb 2010 04:11:17 +0000

Within three days of each other, my grandfather has a birthday and my parents have their wedding anniversary. This week is always something else, something special. I never feel like there’s enough I can do to commemorate this week. Any interesting suggestions?

Phew: DB Moved

steegness — Sun, 10 Jan 2010 07:55:07 +0000

I migrated the whole backend DB of the site to a MySQL 5 server, and I don’t THINK anything went too nuts. Let me know if you see oddities.

Happy birthday, Emma!

steegness — Sun, 22 Nov 2009 02:12:06 +0000

For those keeping score, my eldest daughter has been allowed to live to the ripe old age of ten, as of today. A spa day, a Snuggie, and a cell phone were hallmarks of the day. (Yes, she has her own phone now, lojacked up, of course.)

So to my daughter, upon reaching the turning point of our base 10 numerical system, I wish you a happy birthday, and another awesome year.