🏆 Organized Category Dropdown in Transactions Sheet

Google Sheets Show & Tell
1

Overview

I’ve had a lot of trouble finding the category I want when adding new transactions, so I decided to find a way to organize the dropdown list to make it easier to navigate. I landed on arranging them by Group:

image
image301×1067 29.6 KB

Installation

Sheet is available here: Tiller-Categories-Ext - Google Sheets

  1. Copy the sheet Categories Ext to your Tiller Sheet.
  2. Rename the sheet from “Copy of Categories Ext” to “Categories Ext”

Setup

  1. Once the Category Ext sheet is populated, go to your Transactions sheet. Highlight the Category column, and open the Data-Data Validation menu.
  2. Make sure the Apply to Column field applies to your Transactions-Category column
  3. Make sure Criteria says Dropdown from Range
  4. Type 'Categories Ext'!categories_ext into the Range box

Usage

The dropdown should now show an organized list of categories.

Permissions

Is it ok for others to copy, use, and modify your workflow?

Sure!

2

Thanks for this. So easy to do and useful. But - I had trouble using your named range “categories_ext” when creating the data validation. It kept telling me that the value I was entering from the drop down was invalid. I fixed it by not using the named range and just referencing the range that “categories ext” referenced directly. I also wanted to be able to exclude groups from the pulldown list. I modified the formula in B5 to look at F1:F3 (I only needed up to three exclusions) for group names I don’t want to see. Now is working exactly how I like! This is something I’ve needed for a long time.

3

Thanks for sharing! I also had issues using “categories_ext” as it gave me an error.

The following worked for me and I believe should work for others!

='Categories Ext'!$A$5:A
4

This is a great hack, @alan.heatherley, for a richer experience. Thank you for sharing.

1 Like
5

The named range doesn’t work because a user needs to enter a fully qualified address of =‘Categories Ext’!categories_ext

Seems to be required only when a user copies a template with a named range, so you may need to update your Setup and Installation instructions. I would include the step "rename the copied sheet from “Copy of Categories Ext” to “Categories Ext”. Then the last bullet could be updated to paste the following address to the Range box:

='Categories Ext'!categories_ext
2 Likes
6

Hi @brettanicus! Thank you for your feedback! I’ve updated the instructions to include those points.

-Alan

1 Like
7

Hi, I really like this feature. I am noticing, however, after changing the reference that the lag upon saving when on the Transactions page is very noticeable when entering the category or even adjusting any of the other fields. Has anyone else noticed this behavior? I have >13K rows on Transactions sheet so wondering if that may be driver or if more something with the referencing?