Docs: Account Filter (Excel)

Overview

The Excel Account Filter sheet, based on the popular Sheets Account Filter, is useful if you want to review the transactions of any single account linked to your Tiller spreadsheet. This is a read-only sheet for viewing transactions that also already exist in the Transactions sheet. It’s an alternative if you prefer not to filter your Transactions sheet, but want to review transactions for a specific account.

How to Install the Account Filter sheet

Add Excel Tables to Your Workbook

If you have a newer version of the Excel Foundation Workbook, you can skip this step. Otherwise, you’ll need to add Excel tables to your spreadsheet that contain the contents of the Transactions and Categories worksheets before adding the worksheet.

In each worksheet, simply:

  1. Select the entire used data range (including the header row)
  2. Click Insert/Table in the file menu
  3. Check the “My table has headers” checkbox
  4. Click “Ok” in the Create Table modal dialog
  5. Assign a Table Name in the little text box in the top left of the toolbar— use “Categories” in the Categories worksheet and “Transactions” in the Transactions worksheet

Download the Account Filter v1.01 Workbook

Download the workbook containing the Account Filter v1.01 .
(Version was bumped April 20, 2022.)

Insert the Account Filter Worksheet

  1. Open you linked Tiller Feeds workbook in Excel.
  2. Open the workbook you just downloaded containing Account Filter in Excel.
  3. Right click on the Account Filter sheet in the workbook you just downloaded and click “Move or Copy”.
  4. In the “To book” dropdown, select your linked Tiller Feeds workbook.
  5. Click the “Create a copy” checkbox.
  6. In the “Before sheet” dropdown, select “(move to end)”, then click OK. If you receive an alert about a name conflict, just click Yes.
  7. The new worksheet should appear in your Tiller Feeds workbook with the name “Account Filter (2)”. Rename the worksheet “Account Filter”.
  8. Navigate to “Data / Edit Links…” in the menu (not the ribbon).
  9. Select the link to the downloaded workbook in the box.
  10. Click the “Change Source…” button.
  11. Navigate to the active workbook (i.e. select your personal spreadsheet).

At this point your new template should be functional and linked to your local workbook’s data.

We are still learning how to manage and optimize the sheet insertion process. If you run into any issues or have clarifications to improve the instructions above, let us know!

How to use the Account Filter sheet

To use the sheet, select the account for which you want to view transactions in the drop down in cell B3.

Advanced users can pull in additional columns from the Transactions sheet by modifying the formula in cell B6.

Great sheet addition to Excel. One small issue is that data validation will not copy over from the account filter sheet v1.00 to your personal sheet.

I just copied the data validation source from the account filter sheet to my personal sheet, =Accounts!H2# and the sheet is working as intended! :smiley:

Just noticed this in another share, @TimA. Will see if there is a similar workaround for the Account Filter.

I implemented the change from this post, making the dropdown data validation reference local to the worksheet (in a hidden column at right). The new master is version 1.01 (with the link in the main body :point_up:).

If this change works, let me know… Thanks.

The category description


total does not show the $value. Not sure if it’s a bug or error from my template.