Spreadsheets are great. They are so easy to customize and make them do just what you need. This is why they are so popular for personal finances. It takes minutes to add a column, chart, or new sheet to your spreadsheet to get it to solve an important personal problem— to make it your own.
If you’re feeling generous, maybe you want to share your creation with others. Unfortunately, this can take a little more work since everyone’s spreadsheet is different. Sometimes dates are on column B
and sometimes they are in column G
. In our own spreadsheet, to reference this data, we can just drag over the range to reference it. If we want to share a solution that will work for everyone, however, we need the spreadsheet to find the Date
and reference it where it is.
Though this adds a little complexity, it isn’t that hard.
Let me share our best practices for Tiller-developed spreadsheets…
For this article, I’ve created a simple demo spreadsheet that I will reference. The demo lists the top 20 expenses in the Transactions
sheet within a date range including the category’s group.
Hidden Area
I like to create a “hidden area” off to the right of the main section of my dashboards. In this demonstration, I haven’t hidden these support formulas. If this were in production, I would have hidden F:J
.
Sometimes, I do all sorts of support calculations in the hidden area. In this sheet, I am only finding columns in the Transactions
and Categories
sheets that I want to reference in column I
then building a range string in column J
. In columns G
and H
, I simply type the sheet and header names that I need for each column lookup.
Column Lookups
The workhorse of this process is the formula in I2:I7
. In the cell I2
, the formula is:
=iferror(REGEXEXTRACT(ADDRESS(1,MATCH(H2, indirect("'"&G2 &"'!$A$1:$1"), 0)), "[A-Z]+"))
The truth is, you don’t need to understand this formula to use it. All you need to know is that it looks in the header row of the sheet name in G2
for the header named H2
. I copy it into my templates all the time.
Column Ranges
From there, we create a range string that should look familiar if you build formulas that reference across sheets. In cell J2
, the formula is:
=if(isblank(I2),iferror(1/0),"'"&G2&"'!$"&I2&"$2:$"&I2)
This formula isn’t complicated. It just concatenates text into a range string starting in row 2 and including all rows to the bottom of the sheet (e.g. 'Transactions'!$B$2:$B
)
Using Data-range Strings
Now that we’ve got the range strings for the columns we need, how do we use them?
The INDIRECT()
Formula
The secret to building data references dynamically is the INDIRECT()
function. You may notice that when you write formulas that directly reference ranges in your sheet, each data reference is listed in a unique color.
As you enter direct range references into your formulas, Google Sheets is validating and latching onto them. They cannot easily be changed dynamically. The INDIRECT()
function is an alternate way to create these range references. Essentially, you can build them just as you would any string/text in a spreadsheet— through concatenation and cell references— then the INDIRECT()
function does the hard work of turning that range-referencing text into a direct cell reference that can be used in a formula.
Implementing Column Mapping
You may build your solution by first directly referencing data ranges in your own spreadsheet and get everything working. In my example, the QUERY()
function that creates the dashboard output is in cell A4
. The first argument of the QUERY()
function is a custom array that feeds the column data (from throughout the spreadsheet) into the query.
If I were directly referencing all the data in my personal spreadsheet (i.e. not a shareable solution), the formula would begin like this:
But, the date column (in 'Transactions'!$B$2:$B
in my spreadsheet) may be in a different location in an interested user’s sheet. So I need to replace this reference with a dynamic reference that uses our column mapping formulas. In this case, we simply replace the direct reference to 'Transactions'!$B$2:$B
with INDIRECT(J2)
… and so on. The start of the formula in A4
becomes:
This process is not complicated— especially once you get the hang of it. The main downside, in my opinion, is that the formulas become more inscrutable and challenging to debug.
In Closing…
I hope this helps. Perhaps it will help you share your own dashboards and templates in this community. Perhaps it will help you understand and build on the templates that the Tiller team and others share.
Since this technique is so fundamental to building shareable solutions, I’d love to continue to improve these instructions. If you have any questions or feedback, please let me know.
P.S. This live-build video shows the process in action.