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.8 KB

Installation

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

Copy the sheet Categories Ext to your Tiller Sheet.

Setup

  • Once the Category Ext sheet is populated, go to your Transactions sheet. Highlight the Category column, and open the Data-Data Validation menu.
  • Make sure the Apply to Column field applies to your Transactions-Category column
  • Make sure Criteria says Dropdown from Range
  • Type “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