New foster at the house.
If you’re thinking “what should I get Sean for his birthday?”, I offer:
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!
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.)
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.
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.
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.
For the record, being stuck inside my house this weekend has been hell.
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?