Dear Spreadsheet Whisperer,
I am an operations director for a social movement organization. I’m trying to create a filtered spreadsheet to show expenses related to an org that we provide fiscal sponsorship for. However, when I try to add new criteria to my filters, my whole sheet falls to pieces. Can you help?
Yours, Or Else
Dear OE,
Thank you SO much for reaching out to me! Hearing from activists like you pretty much makes my day. *cue rainbow emojis beaming out of my eyeballs*
It’s SO frustrating when spreadsheets don’t quite do the thing we want them to do, especially when that Thing enables amazing cross-org-movement-collaboration which is so dear to both of us.
First of all, credit where credit is due: your draft template had already come SO FAR! I love to see people in our community using the =IMPORTRANGE() function and you paired it with =FILTER() which is one of my all time favorites.
For readers who may not be familiar, let’s do some review of these building blocks.
Import What?
ImportRange is a powerful Google Sheets formula that copies data from one Google Sheet file to a completely different file (or tab). However, the data cannot be edited in the copied version. It’s basically view-only. This spreadsheet is useful if you want to share some or all of your data with an audience that has different viewing permissions than the original file.
Now, add filters
ImportRange pairs well with the =Filter() function, which lets you determine which rows from the original data should be included in the target spreadsheet. However, to filter your incoming data, you need to pair the Filter function with the INDEX() function. Or Else, you had already done a fantastic job setting this up thanks to a helpful tutorial from TillerHQ. This article from fellow spreadsheet-advice-columnist at KeyCuts takes this solution one leap farther by demonstrating how to apply multiple filter criteria. And even how to take it that much FURTHER by using the =QUERY() function and SQL (a database coding language).
How do we make the filters “speak our language”?
Ultimately, the dilemma came down to classic AND/OR Boolean logic. All of the tutorials that I read (linked above) assume that your filters are cumulative, or in other words, that every row should meet ALL of the criteria in the filter. However, Or Else was looking for a more flexible filter, that every row should meet ONE or the OTHER criterion. So, how do we take an “AND” formula and make it into an “OR” formula?
Well, I tried with an =OR() formula grouping the two criteria, however, I couldn’t get it to pan out. Could have been error on my part, but I ended up moving on!
I realized that this was best classified as a Filter problem and not an ImportRange problem, since the ImportRange data was showing up just fine until I started playing with the filters. I was able to fall back on trusty Ben Collins, google sheets blogger/trainer extraordinaire to find a stellar solution: using the ‘+’ inside of my formula!
In my head, this essentially worked out to, “show me all the alphas” and THEN “show me all of the gammas.” Show me “the alphas AND the gammas.” In spreadsheet lingo, that means show me the rows where Column A = alpha OR gamma.
My favorite tool for teaching Boolean Logic is the ole PB&J venn diagram. See how the “OR” operand is the most inclusive? Any or all apply!
This was a really fun and useful knot to untangle. I had a lot of fun figuring it out and I am so glad that you are unstuck! Also, I think you deserve a real celebration for ALREADY using spreadsheets in such a cool way and having such an ambitious attitude. It takes a lot of courage to reach out to someone you don’t already know to ask for help. Thank you for entrusting me with your dilemma!
xo + you got this!
– Spreadsheet Whisperer