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.
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
Sometimes, I do all sorts of support calculations in the hidden area. In this sheet, I am only finding columns in the
Categories sheets that I want to reference in column
I then building a range string in column
J. In columns
H, I simply type the sheet and header names that I need for each column lookup.
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.
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:
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.
Now that we’ve got the range strings for the columns we need, how do we use them?
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.
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
INDIRECT(J2)… and so on. The start of the formula in
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.
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.