fbpx

Google Sheets Nerdout

So we decided to add blog posts.

“What should I write about?”, asked every reluctant person who was encouraged to write, ever. We agreed that it should be stuff that was relevant to our business, but if we ever found things that we are super passionate about, or found interesting, it might be a good candidate. Besides, why else do we even have a company if we can’t just let fly every so often?

OK, so I have been doing some things for a group of buddies for years on Google, using the sheets function of Google docs. Pretty standard, I know, and I am sure there are formula geniuses out there who can make sheets fly while counting Android sheep. But listen – I figured something out. With the help of a video, yeah, but I’ve been hunting for this answer for a long time, and the irony that I couldn’t find a solution using, well, Google search, is floating out there like a heavy cloud. All I wanted to do was have the text in a column change colour based on the contents of a different sheet in the same document. THIS IS ALL I WANTED.

For example, make the text in this column green if it contained one of the words returned in a lookup. I made it a little more complicated in reality (it’s a superpower, clearly), but man, there was nothing out there in the world that made sense.

And then, I found it.

How amazing is it that people take the time to share knowledge like this?

There’s this thing called INDIRECT, that you need when you want to conditionally format something based on data in a different sheet. I did a little magic using the MATCH function, and then evaluating if it’s a number or not. I know, I know, I should have just intuited that. And you probably knew that already. But it was such a happy, joyous thing to find and get working, I needed to spread the glee. If this makes you think less of me, let’s just pretend this is about building interactive companies in Ontario, or something. But really, I can say that learning how to make the Google suite useful is a big positive for us, as more and more of our partners share documents using cloud services.

But those that found it cool…

I KNOW.

Here’s the resulting formula (if you care):

=ISNUMBER(MATCH($J4,INDIRECT(“MyOtherSheet!$G$5:$G$25”),0))

Ted Brunt