Tutorial: Column Mapping to Make Templates Shareable

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.

Nice tutorial @randy !

This is a complicated topic but you made it easier to understand.

There are some advantages to using this method for your own personal spreadsheets, not just ones to be shared.

If you use the QUERY formula, column letters in the SELECT part won’t automatically adjust if you add or subtract columns in your query range. You can use references to the dynamic column letters, seen in Column I of @randy 's example above.

For example, instead of saying "SELECT A, B"
you could use
"SELECT "&I2&","&I3

Another technique is to use INDIRECT() formulas in the QUERY range, but then in the SELECT statement you can refer to the different columns as Col1, Col2, Col3. If you use the Col approach, you can’t mix and match using the column letters as well.

Jon

1 Like

Great point, @jono. Thanks for sharing this… as I kind of skipped over it.

I too have been bitten many times with lettered, static cell references in QUERY() formulas since they do not update when columns are added as you note.

Randy and Jon, I find that when I share a sheet with others. it is the column lookup formulas that don’t always want to work after copying a sheet. I then have to go to cells with the formulas and just click in the cell and hit enter to kickstart it to work again.

Any tips to make this more bulletproof?

I’ve seen that behavior, @richl, when I replace a sheet in the middle of a dependency chain (including when performing a Restore in the Tiller Community Solutions add-on) like replacing Accounts in this chain:
Balance HistoryAccountsBalances

(This is a flaw in the add-on functionality. I’d fix it if a) I had a solution, and b) the problem happened more frequently (but most of our intermediate dependencies are pretty stable sheets).)

I think that happens because our Restore process deletes Accounts— essentially breaking the references back from Balances— then inserts a new sheet with the same name… and without the formula jostling Sheets doesn’t quite realize the INDIRECT() reference back from Balances is functional again.

I haven’t seen these formulas fail when inserted as a new sheet. I find them pretty reliable.

1 Like

It appears the transfers are not filtered out of the top 20 expenses even though the transfer is hidden in categories.

That would be a cool feature to add, @adekunledauda, and a great next step for anyone who wanted to build on this little tutorial.

If you want to give this a try, you could:

  1. Add the Category/Type to the column lookup area
  2. Add another data column to the literal array in the QUERY() function similar to the one that looks up the group name (i.e. arrayformula(iferror(vlookup(INDIRECT(J5),{INDIRECT(J6),INDIRECT(J7)},2,false)))}) that references the new Category/Type data
  3. Add another WHERE clause to the QUERY() SELECT that filters on Expense type only (e.g. AND Col6 = 'Expense')

Let me know if you pull it off!
Randy