Excel – Setting a Cell Value Based on Background Color

I need to programmatically parse an Excel file where items are grouped with arbitrary group sizes. We don’t want the person filling out the spreadsheet to need to fill in a group # column … so I’m exploring ways to read cell formatting so something like color can be used to show the groups. Reading the formatting isn’t a straight-forward process, so I wondered if Excel could populate a group number cell based on the cell’s attributes.

While it is possible, it’s not a viable solution. The mechanism to access data about a cell cannot be accessed directly and, unfortunately, requires a macro-enabled workbook. The mechanism also requires the user to remember to update the spreadsheet calculations when they have finished colorizing the rows. While I won’t be using this approach in my current project … I thought I’d record what I did for future reference.

We need to define a ‘name’ for the function. On the “Formulas” tab, select “Name Manager”.

Select ‘New’

Provide a name – I am using getBackgroundColor – and put the following in the “refers to” section: =GET.CELL(63,INDIRECT(“rc”,FALSE))

Now we can use this name within the cell formula:

Select the rows for your first group and change the “fill color” of the row.

Repeat this process to colorize all of your groups – you can re-use a color as long as adjacent groups have different colors. Notice that the “ColorGroup” values do not change when you colorize your groups.

On the “Forumlas” tab, select “Calculate Now”

Now the colorized cells will have a non-zero value.

Insurance, pt 2

We finally got around to calling Progressive about the difference in my quoted price v/s their renewal price … ugh! The first person we spoke to started out with ‘just cancel the old policy & open the new one’. Which, great — except it’s a pain. And we lose the ‘perks’ that come with being a long time customer. Including some 250$ off the deductible. Start a new policy, have the bad luck to get in an accident, and saving 150$ just cost me 250$. Not a great deal, that. Plus our home insurance is tied in with the car insurance. And the only reason I didn’t just buy insurance from GEICO (who had the lowest quoted price) is that I didn’t want to screw with the home insurance right now.

Luckily, she transferred us to an insurance agent for help. He checked and there was no way they could price the policy we had at the price quoted under my name. He was able to move the reduced deductible over to the new policy (although it’s still a pain that they have no provision for just swapping the names … and it’s even more of a pain that the person listed first makes such a difference in pricing!). Final price was 164$ for more coverage than the policy they wanted 321$ !?!?! Only took an hour and a half to get there! And I’ve got a really bad feeling the same thing can be done with the homeowners insurance. 🙁

How much fabric *do* you need for a peppermint swirl dress?

I love the Peppermint Swirl Dress, and I’ve made a few of them for Anya. The 5-year-old size easily fit within four yards of fabric — two yards of each colour. The 8-year-old size fit easily in six yards of fabric — three yards of each color (and about a half-yard of one color was left over, the half-yard from the other was used for the top of the dress). I think the 10-year-old size will be tight with six yards of fabric … but it’ll be a few years before I know for certain 🙂

Disbarment and Judicial Estoppel

As the impeachment not-quite-a-trial wraps up, I am left wondering if normal processes are applied to Senate impeachment hearings. Can, for instance, a lawyer get disbarred for standing before the Senate and making false claims? Does judicial estoppel apply when a defendant is simultaneously asserting X and NOT X in the impeachment and another legal proceeding?

Parade of Horribles

A parade of horribles is not always a fallacy . Yes, the rhetorical device is often used in inappropriate manners; but the fact it can be misused does not invalidate the technique in toto. The parade of horribles which stems from accepting the parade of horribles as a valid reasoning tool do not render the rhetorical device a logical fallacy. When someone marches out this particular class of argument, the validity of the argument needs to be determined in its specific instance. Horrors which will occur either way do not make a persuasive argument. Horrors which are very likely to occur and are actually horrible compared to any benefit from the argument? The parade is a legitimate argument.

I find myself thinking a lot about these parades while watching the Senate Impeachment trial. There are horrifying consequences to accepting some of the Defense’s positions. Dershowitz proclaims that “Every public official that I know believes that his election is in the public interest; and, if a president does something which he believes will help him get elected in the public interest, that cannot be the kind of quid pro quo that results in impeachment.” How is that a reasonable position? Not holding the next election is “in the public interest” … but quite clearly an offense against the core tenants of this country.

Their argument fell apart a bit because it essentially exonerates Nixon — he wanted to get re-elected “in the public interest”, had people break into the Watergate to increase his re-election chances … and saying that Nixon is different because he destroyed evidence is a laughable contortion. Didn’t he destroy evidence in the nation’s best interest too?

OpenHAB CalDav Personal Binding – Item Name Filtering

We use the CalDav Personal binding to select items from our Exchange calendar to populate date/time OpenHAB Items — when does Anya have her next gymnastics class, when is the next Trustee meeting, etc. When we had first set this up, we were using manually created appointments. The appointments were assigned unique categories so the binding could determine which appointment should be used to update the Item. I’ve since started creating calendar items based on published calendars (so far a Google calendar and a SchoolPointe calendar), but the Python module for interacting with Exchange cannot assign a category to the appointments it creates.

The binding allows you to filter on the appointment subject (‘name’) using a regex. The binding documentation says to use name-filter:’\<Some Filter\>’ which … well, doesn’t work. We tried omitting the back-slashes in case they were meant to be escape characters. We tried omitting the greater and less-than symbols in case those were meant in the way I often use them, to designate <the part you replace>. Still doesn’t work. We tried using forward-slashes instead of backslashes because that’s the normal regular expression syntax. Nope. We tried adding a ‘starts with’ ^, trailing .* to ensure it would match anything that started with what we wanted. Nope. We’d alternately match all of the appointments or none.

Consulting the source, there is a very restrictive character set available in your name filter regex. The binding uses Java’s Matcher with a regex to extract your regex from the item configuration. You need to have filter-name: then you may have a single quote (‘? means 0 or 1 of ‘). This is Followed by one or more characters from the class which is all upper and lower case letters A-Z, a full stop, an asterisk, a plus sign, a minus sign, a space, and a pipe bar. Then you may have another single quote. The bit with one or more characters from the restricted class is extracted — this is how the binding gets your regex from the item config.

private static final String REGEX_FILTER_NAME ="filter-name:'?([A-Za-z\\.\\*\\+\\- \\|]+)'?";

Using unsupported characters in your filter-name regex alternately match all appointments or none. Using filter-name:’\<test>\’ (as the documentation literally instructs me to do) doesn’t return a match with anything as + requires one or more matches from the character set. I have zero such characters after the opening single quote. Similarly filter-name:’^Beginning of string.*’ doesn’t return a match. It appears that, in cases where the name filter is null … all items are matched. Explains why we were getting the same appointment’s details posted into each item.

On the other extreme — a filter like filter-name:’Pick up dry-cleaning at 1 Main Street’ will truncate your regular expression at the number character. The extracted matched group is Pick up dry-cleaning at … which won’t match anything unless you actually have an appointment titled “Pick up dry-cleaning at ” with a trailing space. I’ve seen posts on the OpenHAB forum where individuals have non-English words in their match … filter-name:’Trip to Askøy’ which, again, match nothing since the actual regex used by the binding is Trip to Ask  The same thing happens when looking for character classes (i.e. I don’t know if this will be capitalized, so I want to match [Tt]est).

The solution, since a question mark isn’t an option, is to use a plus or splat to replace any character that isn’t supported by the binding. Using a plus ensures there’s something where you expect the character to occur, although the * is a broader match (we use “Township: Event Name”, but I don’t need the colon to successfully match my item. “Township Event Name” would match. I could even use a different delimiter as “Township, Event Name” would also match). Where you are unsure of the case, you need to use a pipebar (e.g. filter-name:’Test|test’)

The Items that are populated with the start time and event name for the next Township meeting look like this:

DateTime Calendar_Upcoming_Township "Upcoming Township meeting (start) [%1$tA, %1$tB %1$te, %1$tY at %1$tl:%1$tM%1$tp]" <calendar> (gCalendar) {caldavPersonal="calendar:ourcalendar type:UPCOMING eventNr:1 value:START filter-name:'Township. .*'"}
String Calendar_Upcoming_Township_Title "Upcoming Township meeting [%s]" <calendar> (gCalendar) {caldavPersonal="calendar:ourcalendar type:UPCOMING eventNr:1 value:NAME filter-name:'Township. .*'"}

And the calendar events titled “Township: Trustee Regular Meeting” or “Township: Craft Fair” are all identified by the filter.

Note: Scott submitted a PR to change the regex used to extract your filter-name regex. Once this change gets merged, you’ll be able to use character sets (e.g. [T|t]est), numbers, and ‘special’ characters excluding the single quote. Including the single quotes around the filter will be required.

Chapter books are (not) boring

Anya wouldn’t read chapter books. Not even one about Spirit, and she’d almost eat mushrooms if they had Spirit on them. The books, she would tell me, are boring. Which is odd since (1) she’s not read it … so how would she know it is boring!?! and (2) it’s Spirit. She’ll watch the same episode of Spirit a dozen times without finding it boring, how in the world is a book based on the show prejudged as boring?

The funniest thing, though, is the ’emerging reader’ Spirit book? She tore through that. It was the longer chapter book format she objected to. Turns out the elementary school wouldn’t let K kids take out chapter books because they were too long/involved/hard, and the way they communicated this got translated to “chapter books are boring and hard” in my daughter’s head. We had a bunch of free books available from this Kellogg’s Feeding Reading promotion — had to use them by November, so I randomly picked a bunch of books from their site. One of which was this Lucy and Andy Neanderthal graphic novel. She absolutely loved the thing — read it over and over. And asked for the other two books in the series. Totally! Except I didn’t want to pay 15$ for the hard cover book 2 or 3. A quick search yielded an outlet site that had the hard cover books for 3$ each. Book Outlet has a referral – 10$ off your first order of 25$ or more (and I get a bonus 10$, which does not negate the fact they’ve got awesome prices) … I got a few books for myself to hit the ‘free shipping’ level. Anya has been reading the three Neanderthal books in cycle since they’ve arrived.