Transactions Sheet Spotlight for Google Sheets

Overview

The Transaction Sheet Spotlight :flashlight:is like a window that you create at the top of your Transactions Sheet to obtain an at-a-glance view of entries that might be similar to a flagged or uncategorized transaction.

It’s not a template to be loaded, but rather a set-up process with two formulas to achieve the goal of uncovering these records right on the Transactions sheet itself.

I often look at a records in the Transaction sheet in Tiller and want to know things like:

  • How much did I spend on that last time, or the last couple of times?

  • How did I categorize this type of transaction in the past?

  • Is that a new merchant? Have I paid them before?

  • Is this bill higher than usual?

While there are certainly lots of ways to deep dive on these questions, sometimes I just want a quick answer. And I don’t want to have to do much to get there. Like I don’t want to leave the sheet or apply a filter, or use a search command.

I find it especially useful as a solution for uncategorized transactions that I just don’t see often and don’t automatically categorize. Those are the ones that I’m usually scratching my head about what category to pick. I want to have a quick look back at what other similar transactions came before it. That way I can be effective and consistent in my categorization. And I want to search nearly automatically, “in-sheet”.

From that goal, I developed what I call the Transactions Sheet Spotlight.

So you might be thinking :thinking: - how am I going to display searched transactions in the Transaction sheet without impacting the Tiller feed or reports?

The key concept here is that the dates of searched transactions are displayed with a text version of the date with one or more leading underscores.

1/1/2024 becomes __1/1/2024

With this construct, the Tiller feed remains unaffected because it does a sort descending by date at the end of the process and these searched transaction dates with leading underscores always remain on top.

If you do not sort your transactions descending by date (newest on top), then this solution may not work for you.

If you enable the Tiller feature called “Auto Sort Override”, the solution should still work because Tiller adds new transactions to the bottom of the sheet and then skips the sort. However, it would be up to you to make sure you don’t sort the Transactions Sheet Spotlight rows away from the top.

As far as reports are concerned, I’ve found no adverse impacts so far. Most reports query data based on a valid date range. The searched transactions in the spotlight area are not interpreted as dates by the query function. They won’t fit into any logical report criteria. I cannot make any claim for the entire Tiller universe of community solutions, but I have not found a conflict so far. Further, the spotlight can be turned OFF and only utilized while actively processing your transactions.

The following screenshot shows a suggested set-up. The first column returns hyperlinks that allow you to jump directly to each matching row.

Installation, Setup, and Usage

To implement the spotlight, there are three basic steps with two main formulas to apply. And one note of Caution that you should understand via video or read below (refer to Step 3. and FAQ) before diving in.

If you prefer a walkthrough video, you can check it out below. Run-time is less than 10 minutes and it will guide you through the set-up and use.

YouTube Video Link: Transactions Sheet Spotlight for Tiller

The following shared Google Sheet template contains a Transactions sheet with the default spotlight applied, as shown in the video. You may make a copy and then transfer the formulas if you prefer that method. Otherwise the formulas will be provided in the write-up below.

Google Sheets template: Transactions Sheet Spotlight - Tiller

Step 1. Create the Spotlight Window

You are free to size your solution however you’d like, but this tutorial will walk through adding a 10-transaction spotlight window.

Start by inserting 11 rows (one header plus ten result rows) between Row 1 and the first transaction in your Transactions sheet in Tiller.

You can perform the insertion action by clicking on a group of the first 11 rows and then right-clicking on “Insert 11 Rows above”. Next you can group rows 3 through 12 (highlight the rows and right-click “View more row actionsGroup Rows 3-12”). Lastly you can highlight row 12 and navigate the menu bar to “ViewFreezeUp to Row 12”. By now you should have something that resembles this:

Notice that I’ve apply a two-tone purple color scheme but you can feel free to apply the color (or lack thereof) that most satisfies you. The first row is different because it will contain the search term, on/off control, and other summary stats that you may desire. I’ve made it a darker shade with a white text.

Step 2. Set-up the First Row

Starting with Cell A2, create a dropdown that will serve two purposes. (1) It will act as an ON/OFF switch, activating and de-activating the spotlight. (2) When ON, it will determine the length of the search term. More on this later.

For now, you could follow a set-up such as the one shown in the image below, but you can totally customize to your liking later on. To get to this menu the first time, choose menu path DataData Validation. Then with Cell A2 selected, click the Add Rule button.

After the initial set-up, it’s easy to jump straight to editing the rule by clicking the small pencil icon in the lower right corner of the dropdown.

In the Date column row 2, enter the date text “_12/31/5000”. The premise is that all of the dates in this spotlight window will have leading underscores. When sorted descending, they will always appear at the top. This date in December of the year 5000, will ensure that our search row will stay in Row 2.

In the Description column, row 2, enter the first of two major formulas as shown below:

=LET(start_row, 13, stop_row, , flagchar, "t", flagcol, 1, A2_override, "", search_override, "", 
on, IF(AND(A2_override<>"",A2<>"OFF"),A2_override,A2),
cat_col_num, MATCH("Category",1:1,0), date_col_num, MATCH("Date",1:1,0), descr_col_num, MATCH("Description",1:1,0),
cat_col_let, REGEXEXTRACT(ADDRESS(start_row,cat_col_num,4),"[A-Z]+"), date_col_let, REGEXEXTRACT(ADDRESS(start_row,date_col_num,4),"[A-Z]+"), descr_col_let, REGEXEXTRACT(ADDRESS(start_row,descr_col_num,4),"[A-Z]+"), flag_col_let, REGEXEXTRACT(ADDRESS(start_row,flagcol,4),"[A-Z]+"),
IF(on="OFF","Spotlight is OFF, set A2 to a value > = 1",
IF(search_override<>"",search_override,IFNA(LEFT(QUERY({INDIRECT(descr_col_let&start_row&":"&descr_col_let&stop_row),INDIRECT(cat_col_let&start_row&":"&cat_col_let&stop_row),INDIRECT(flag_col_let&start_row&":"&flag_col_let&stop_row),INDIRECT(date_col_let&start_row&":"&date_col_let&stop_row)},
"select Col1 where Col3 = """&flagchar&""" or Col2 ="""" order by Col3 desc, Col4 desc limit 1",0),on),"No Selection"))))

This formula pulls the search term from one of two sources. First it looks for any row that is flagged for the spotlight with a flag character. The flag character by default is “t” but can be user-defined. If multiple transactions are flagged, it takes the first in the list (chronologically newest when sorted date descending). Second, if no transactions are flagged, it looks for any uncategorized transactions and takes the highest in the list. Once you categorize that transaction, the next uncategorized one gets picked up automatically.

TIP: When you copy and paste this formula, make sure there is a line break after “search_override, “”” (use Cmd + Return on Mac, Ctrl + Return on Windows). You’ll want all of the variables declared in the beginning of the LET statement to be visible, up through search_override. This way they’ll show up even when you completely minimize the formula bar to a height of one line. Though they can be left as default, they are the main inputs that you may want to adjust later on. We don’t want them buried in the formula below the visible line.

Here is an explanation of each:

start_row - Defines first row of data. Update only if you size your window for other than 10 rows.
stop_row - Defines last row of data. Leave empty to consider the whole sheet.
flagchar - Defines flag character you’ll use to flag a transaction for search.
flagcol - Defines the Column number where you will place the flagchar.
A2_override - Used to free type a number within quotes to override the number selection in A2.
search_override - Used to free type a search term as a text string. This will override any flagged or uncategorized transactions that otherwise might be used for the search term.

The idea of the numbers in Cell A2 is to set the length of the search term. For example, a value of 5 will return the first 5 characters of the Description in the flagged or uncategorized line. This length may impact the number of results that come back. That’s because the search can get more specific as more characters are included. I generally find that 5-15 is an optimal search length for me personally. You can add a new dropdown item or use A2_override to customize this search term length to your liking. Just remember to reset it if you use A2_override as it will continue to override until cleared.

And now for the last step.

Step 3. Enter the Main Search Formula

In Cell A3, enter the second major formula which is the main search formula. Before you copy/paste it, read the following word of caution.

CAUTION :warning: - If you have added column(s) in your Tiller Transactions sheet such as Group or Type that fill the entire column based on a lookup of the Category in another sheet, then you may need to make some adjustments. Otherwise, you could run into a Circular dependency error. This occurs because the spotlight is trying to query that column while at the same time that column is trying to get an input from the Category column in the spotlight window. Refer to the FAQ for a detailed explanation of three possible remedies for this situation.

With that out of the way, here is the second formula:

=LET(numresults, 10, endrow, , lastcol, "G", gid, "#gid=1256593101", date_format, "m/dd/yyyy",
beginrow, numresults+3, last_col_num, COLUMN(INDIRECT(lastcol&1)), date_col_num, MATCH("Date",1:1,0), descr_col_num, MATCH("Description",1:1,0),
search_cell, INDIRECT(REGEXEXTRACT(ADDRESS(1,descr_col_num,4),"[A-Z]+")&2), lastrow, IF(endrow="",COUNTA(INDIRECT(ADDRESS(beginrow,date_col_num,4)&":"&REGEXEXTRACT(ADDRESS(beginrow,date_col_num,4),"[A-Z]+")))+beginrow-1,endrow),
IF(OR(A2="OFF",search_cell="No Selection"), MAKEARRAY(numresults,date_col_num,LAMBDA(r,c,IF(c=date_col_num,"___0/00/0000",))),
LET(qe, QUERY({INDIRECT("A"&beginrow&":"&lastcol&lastrow),ARRAYFORMULA(ROW(INDIRECT("A"&beginrow&":A"&lastrow)))},
"select * where Col"&descr_col_num&" contains """&search_cell&""" order by Col"&date_col_num&" desc limit "&numresults&"",0),
qe_count, COUNTA(CHOOSECOLS(qe,date_col_num)),
LET(qrange, IF(qe_count<numresults,IFERROR(VSTACK({qe,ARRAYFORMULA("__"&TEXT(CHOOSECOLS(qe,date_col_num),date_format))},MAKEARRAY(numresults-qe_count,last_col_num+2,LAMBDA(r,c,IF(c=last_col_num+2,"___0/00/0000",)))),"___0/No Result"),
{qe,ARRAYFORMULA("__"&TEXT(CHOOSECOLS(qe,date_col_num),date_format))}),
hyper_col, ARRAYFORMULA(IF(CHOOSECOLS(qrange,last_col_num+1)="","",HYPERLINK(gid&"range=A"&CHOOSECOLS(qrange,last_col_num+1),CHOOSECOLS(qrange,last_col_num+1)))),
ordercols, SPLIT(SUBSTITUTE(REGEXREPLACE(JOIN(",",SEQUENCE(1,last_col_num-1,2,1)),"\b"&TO_TEXT(date_col_num)&"\b","Σ"),"Σ",last_col_num+2),","),
{hyper_col,CHOOSECOLS(qrange,ordercols)}))))

Don’t panic if you initially receive an error. Often this has to do with the user-defined inputs. If not, reach out to me and I’ll try to help. Let’s talk again about those variables that should show up on the first line of the LET function. When you paste the formula, and before you hit enter (if you’ve already done it, it’s ok, just delete and start over), make sure there is a line break after date_format.

Here is an explanation of each user-defined variable:

numresults - Defines number of search results. Make sure it matches the size of window that you created.
endrow - Defines last row of data. Leave empty to consider the whole sheet.
lastcol - Defines the last column of transaction data, looking left to right, that will be returned in the window.
gid - Defines gid of your Sheet URL, used to build valid hyperlinks. Replace the numbers with the value from the end of your sheet URL.
date_format - Defines format of the displayed date strings in the results.

This formula runs a query against the defined range of rows, using the defined search term. It returns matching results sorted date descending, up to the limit defined by numresults. Column A returns a hyperlink for any matching results. If there are fewer results than the limit, the remaining rows contain a placeholder date “___0/00/0000”.

An aesthetic tip is to set a conditional formatting rule on the spotlight window that effectively makes these zero date placeholders disappear by setting the text color equal to the background color.

One More Enhancement

Let’s say you want to see a summary stat like the Average of the Amount returned for the matching transactions. No problem. Enter a formula in the Amount column in row 2. For example, the following formula can be used to calculate the Average of the returned transactions. It includes error handling so that it appears blank when the spotlight is OFF or when no transactions match.

=IFERROR(AVERAGE(E3:E12),)

And that’s it. Turn the spotlight to OFF in Cell A2 and collapse the rows. Fire it back up next time you’re ready to use. Enjoy!

Permissions

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

Notes

Some Words on Performance (speed)

Even though these formulas are a bit lengthy, I did make an effort to keep them efficient while balancing with the desire for flexibility of user inputs and accommodating different sheet layouts. My experience in testing the solution for performance of my Tiller Transactions sheet went like this.

I started with a transactions sheet of around 7,500 rows with data going back to 2019. The performance was ok but nothing to “write home about”. It would take 4-5 seconds for changes to display in the spotlight and with the dreaded progress bar in the top right of the sheet. Truth be told I experienced lag even before implementing this solution.

After a lengthy process of tweaking formulas, I decided to check out all other possible bottlenecks in the sheet. I made sure to minimize volatile functions and remove unnecessary conditional formatting. But of all the changes that I made, what mattered the most was to trim back the number of rows. I archived some of the past years’ transactions into another sheet.

I am now running with two years of data consisting of approximately 1,500 transactions. The outcome is a much more acceptable performance with no progress bar appearing and changes happening generally within 1 second. After trimming the data, I found that tweaking the formulas made no noticeable difference. I hope that insight helps if you find that the performance is lagging. I do experience some additional lag on an 8 year old laptop with lesser processing power and using a wifi connection. In the end, I expect that performance is likely to vary.

FAQ

Q1: I’m getting a Circular Dependency Error. What can I do to fix it?

TSS - Sample Cir Dep Error

A1:

This error is most common if you have a lookup column(s) like the Group or Type column in your Transactions sheet. These can have a calculation that fills down across the spotlight window and references the Category column, thus creating a conflict.

There are three possible remedies:

Remedy 1:

Move the formulas and create even more functionality with extra search features

Move the fill down formula for those calculated columns from Row 1 to the row below the spotlight window. That row would need to also have a placeholder date in the Date column to avoid getting sorted out of place.

This is my preferred solution because it can be combined with some extra features that turn this spotlight window into a tool to search by other columns, not just the Description.

There is a 2nd tab in the provided template called “Transactions Spotlight_alt” that contains the layout for this solution (including the Group and Type formulas).

The suggested changes include:

  • Insert a new Row 13. Make sure to put the value ___0/00/0000 in the Date column for that row. (It can be blended into the background as with the others).

  • Place the new Group and Type formulas (or others you may have created) in the new row.

Formula for Group:

=arrayformula(if(row($D$13:$D)=13,"Group",iferror(vlookup($D$13:$D,{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))

Formula for Type:

=arrayformula(if(row(D13:D)=13,"Type",iferror(vlookup(D13:D,{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Type",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Type",Categories!$1:$1,0),4),1,""))},2,FALSE),"")))
  • Make a dropdown in A13 with searchable column names like “Tags”, “Category”, “Full Description” (i.e. columns containing text entries that you may want to search). No need to enter “Description” as it will be the default when this cell is empty.

  • Point the user-defined variable search_override in the first major formula in Row 2 of the Description column to Row 13. This is where you can enter a search term like a Tag. It will only be used when you’ve entered some text. This means entering C13 (or whatever column your Description is in) after search_override in place of the empty string quotes.

  • Replace the major formula in A3 with the following updated formula and change the first line user-defined inputs as necessary - for example you should now be able to comfortably set the lastcol to the last column of your sheet without concern for interferences.

=LET(numresults, 10, endrow, , lastcol, "R", gid, "#gid=1256593101", date_format, "m/dd/yyyy",
search_col, IF(A13="","Description",A13),
beginrow, numresults+4, last_col_num, COLUMN(INDIRECT(lastcol&1)), date_col_num, MATCH("Date",1:1,0), descr_col_num, MATCH("Description",1:1,0), st_col_num, MATCH(search_col,1:1,0),
search_cell, INDIRECT(REGEXEXTRACT(ADDRESS(1,descr_col_num,4),"[A-Z]+")&2), lastrow, IF(endrow="",COUNTA(INDIRECT(ADDRESS(beginrow,date_col_num,4)&":"&REGEXEXTRACT(ADDRESS(beginrow,date_col_num,4),"[A-Z]+")))+beginrow-1,endrow),
IF(OR(A2="OFF",search_cell="No Selection"), MAKEARRAY(numresults,date_col_num,LAMBDA(r,c,IF(c=date_col_num,"___0/00/0000",))),
LET(qe, QUERY({INDIRECT("A"&beginrow&":"&lastcol&lastrow),ARRAYFORMULA(ROW(INDIRECT("A"&beginrow&":A"&lastrow)))},
"select * where Col"&st_col_num&" contains """&search_cell&""" order by Col"&date_col_num&" desc limit "&numresults&"",0),
qe_count, COUNTA(CHOOSECOLS(qe,date_col_num)),
LET(qrange, IF(qe_count<numresults,IFERROR(VSTACK({qe,ARRAYFORMULA("__"&TEXT(CHOOSECOLS(qe,date_col_num),date_format))},MAKEARRAY(numresults-qe_count,last_col_num+2,LAMBDA(r,c,IF(c=last_col_num+2,"___0/00/0000",)))),"___0/No Result"),
{qe,ARRAYFORMULA("__"&TEXT(CHOOSECOLS(qe,date_col_num),date_format))}),
hyper_col, ARRAYFORMULA(IF(CHOOSECOLS(qrange,last_col_num+1)="","",HYPERLINK(gid&"range=A"&CHOOSECOLS(qrange,last_col_num+1),CHOOSECOLS(qrange,last_col_num+1)))),
ordercols, SPLIT(SUBSTITUTE(REGEXREPLACE(JOIN(",",SEQUENCE(1,last_col_num-1,2,1)),"\b"&TO_TEXT(date_col_num)&"\b","Σ"),"Σ",last_col_num+2),","),
{hyper_col,CHOOSECOLS(qrange,ordercols)}))))

Here’s what it would look like with the relocated Group and Type formulas, plus extra search functionality. This example shows searching the Tags column for the tag “HSA”.

Remedy 2:

Limit the output columns to avoid the interference

Adjust the spotlight formula user-defined input lastcol in cell A3 to a column that doesn’t overlap those special calculated columns. For example, the formula defaults to lastcol “G” but you can overwrite it so that the spotlight only returns columns prior to those special calculated columns. However, note that the Group and Type columns also reference the Category column and you would also have to therefore avoid the Category column. That makes this solution not so favorable if your goal is to see at-a-glance what category you used last time.

Remedy 3:

Keep the formulas in Row 1, but adjust them to skip the spotlight window

Keep the formulas in Row 1, but adjust them to skip the spotlight window. Alternative formulas to achieve this for the Group and Type column are provided below. Adjust the first three user-defined variables as necessary.

Skip Formula for Group:

=LET(start_row, 13, end_row, , cat_col, "D", {"Group";MAKEARRAY(start_row-2,1,LAMBDA(r,c,));arrayformula(iferror(vlookup(INDIRECT("$"&cat_col&"$"&start_row&":$"&cat_col&end_row),{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,"")&"$2:$"&SUBSTITUTE(ADDRESS(1,MATCH("Group",Categories!$1:$1,0),4),1,""))},2,FALSE),""))})

Skip Formula for Type:

=LET(startrow, 13, endrow, , cat_col, "D",
target_range, cat_col&startrow&":"&cat_col&endrow,
{"Type";MAKEARRAY(startrow-2,1,LAMBDA(r,c,));arrayformula(iferror(vlookup(INDIRECT(target_range),{indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")&"$14:$"&SUBSTITUTE(ADDRESS(1,MATCH("Category",Categories!$1:$1,0),4),1,"")),indirect("Categories!$"&SUBSTITUTE(ADDRESS(1,MATCH("Type",Categories!$1:$1,0),4),1,"")&"$14:$"&SUBSTITUTE(ADDRESS(1,MATCH("Type",Categories!$1:$1,0),4),1,""))},2,FALSE),""))})