Formatting with Multiple Conditions in Google Sheets

In my Etsy adventures, I'm always trying to find ways to improve my productivity. Streamlining my process for finding good tag phrases for my Etsy listings has been one of my main focuses!

The Problem

I've been tinkering with Google Sheets for a while now and I've built a large tag database. Since the file has become so large, it is cumbersome to look at several columns of data!

I want to be able to look at only the tag column and determine at a glance if that word has good stats.

The Hypothesis

The pseudo code/ map of what I had in mind looked something like this:

for columnA (Tag) 
if columnB (Competition) is "Very Low" 
and
columnC (Engagement) is "Very High"
then
color columnA Bright Green

Basically, I want the cells of column A to be bright green when column B is "Very Low" and column C is "Very High." Low competition and high engagement; the sweet spot!

The Solution

Low and behold, I discovered Able Bits, which posted about this exact scenario. Huge thank you to them!

In short, I used the AND function to achieve the formatting I wanted. It does exactly what I was hoping for: formats the color of cells in column A when conditions from columns B and C are met.

=AND($B:$B="Very Low",$D:$D="Very High")

The End Result

I set up multiple formatting conditions for column A. Since there won't be a large number of ideal keywords that fit the criteria perfectly, I wanted to make a scale of colors going from red (meets the least requirements) to Bright Green (meets all the requirements.)

Now I can look at one column to determine which keywords I want to use instead as opposed to several; saving lots of time and energy (and eye strain!)

Apple Numbers Functions and Syntax Examples

In my Etsy Adventures, I've been trying to find what workflow works best for the various types of tasks involved with being an Etsy seller.

I moved over to Google Sheets for most of my spreadsheet work, but I found that Apple Numbers is actually quite powerful! I can see myself using it in the future, so I wanted to document some of the formulas and syntax used in my Etsy spreadsheet work.

[Google Sheets] Filter Multiple Conditions

In my Etsy adventures I've collected a huge list of keywords (12,000+!) I use a combination of Marmalead and Etsy Rank for my keyword research and use Google Sheets to organize them.

The Problem

In this keyword acquisition exercise, I ended up with a ton of keywords that were generally relevant, but didn't quite describe my particular product. So I've been looking for ways to isolate only the keywords that are relevant to the products that I offer right now, without losing keywords that may be relevant later. My thought was to use the Google Sheets "filter."

tackling multiple conditions

I quickly found that within the GUI of Google Sheets, it wasn't an option to filter by multiple conditions; phrases that contain only certain words. Basically a series of OR logic statements/conditions.

use pseudo code to plan (I'm old)

I'm from the old school and I almost always need to write pseudo code to visualize and plan how I'm going to approach a problem. It looked something this like this:

In column A, only show results that contain the words, "perler, nintendo, gift, geek, or nerd"

'REGEXmatch' formula

Now that I know what I'm doing, I found that I can use a regular expression in the filter custom formula input to tell Google Sheets to filter the results using the required multiple conditions.

There is plenty of literature about regex within the Google Sheets documentation if you're curious about why this works. The important thing to pay attention to are the terms separated by the "|" (pipe) characters. This is where you will enter your list of terms to filter by.

Save as Filter View

Make sure to save this filter view so that you can toggle these conditions easily!

Filtered view mode has these grey borders.

Save as Filter View

Centered Focal Point

Preface

I've been working for Vodori for almost two years now, and I've come across some crazy CSS puzzles. This one is no exception.

Process

Our experience design team (known internally as the "XD" team) had worked with our client on establishing information architecture, content strategy, and design. As many organizations operate, the XD team had finalized the designs and then handed them off to the developers, with some consultation to ensure a smooth transition. The puzzle we will solve here is from one of the many templates designed by the XD team.

Requirements

• The layout should be two columns, text on the left, image on the right.
• The columns should take up 50% of the available space each
• The text area must accommodate an unknown amount of text.
• As the user adds text to the left column, the image on the right should grow and fill the visible space.
• The focal point of the image should always be in the center.
• Must work in legacy IE (IE9+)

Things we know

• To meet the image requirement for center focal point of the image, and filling the available space, we're going to need to make it a background. We can use background-size: cover to fill the space, and background-position: center to keep the focal point in the center. Oh, we'll need to add no-repeat, since we... don't want it to repeat. xD
• We should limit the use of css properties that take elements out of the document flow, since we want stuff to grow with the flow of the DOM elements. So we probably won't be using positioning rules, or position: absolute.

Let's build this thing!

Before we dive in, let's go back to basics. We need a layout that allows both the text and the image to grow and be the same height all the time. So what do we need?

• Wrapper
• Container for the image
• Container for the text.

How do we allow for two nodes to grow with each other? Allow the child to grow so that it increases the height of the parent. We could set it up like this:

wrapper
  image
    text

On a basic level, knowing we're using background images, we could have one large box with a background image set on the parent, then as we add text to it, the background image will continue to fill the available space, and be centered (using the css approaches we outlined earlier). The only problem we have is how to position the elements. Perhaps it will become more apparent as we build it, so let's try it!

Let's build our foundation of HTML

<div class="wrapper"> <!-- The Wrapper -->
  <div class="image"> <!-- The image needs to be able to grow as we add text, so it needs to be the parent node -->
    <div class="text"></div> <!-- The text container -->
  </div>
</div>

---

The image

Requirements

• 50% width
• Right side of the container
• No Repeat
• Centered focal point

The CSS

.image {
  width: 50%;
  height: 100%;
  background-image: url(path);
  background-position: center;
  background-repeat: no-repeat;
  background-size: cover;
  float: right;
}

Take note of the float: right. This is important because without it, our layout will not work. This positions the image to always be positioned on the right.

The text

Requirements

• Needs to be on the left side
• Contains variable amount of text
• Height of the container is always the same as the image

.text {
  box-sizing: border-box;
  width: 100%;
  margin-left: -100%;
}

We need to set the width to be 100% since it's the child of image, which is 50%. Setting .text to 100% is basically setting the width to 50% (which is what we want).

Remember, we need to make .text a child of .image so that when we add text to it, it will increase the size of .image. If we decouple them, they no longer affect the other's height like we need them.

Next, we need to offset our .text. We are going to use margin-left here since it doesn't require to declare position: relative as we would need to do with using the left property.

At first glance, you might think that we need to offset it by -50%. But that only sets it relative to the parent's width (.image) which is 100%. So by setting -50% offset for text, we're only moving it over to the left half way. To make it go all the way to the left, we need to set the offset to -100%.

Voilà!

See the Pen Centered Focal Point by Nico Killips (@kernme) on CodePen.