When building custom sheets for Tiller using Google Sheets, there are two approaches you can take when referring to the data in columns: fixed or dynamic. When Tiller and Tiller Labs build sheets, we use the dynamic method. We encourage you to dynamic references as well.
In this post, we’ll show you exactly how they work.
Here’s a quick overview:
|How To Use||Use the Column Letter||Use MATCH, CHAR and INDIRECT functions|
|Example of usage||=Transactions!A2:A||=INDIRECT(M5)|
|Pros||Simple to Use||Formula works no matter how columns are arranged|
|Cons||Formulas won’t work if columns are arranged differently||Adds an extra step & makes formulas a harder to read|
Fixed Column References
The simple, fixed way is to just refer to cells using their column letters.
For example, if in your
Transactions sheet, Column D is the
Category and Column E is the
Amount, then you can get a Sum of all your Food category transactions using the formula
E:E refer to the entire contents of Column D and E, respectively. The nice thing about this reference is it doesn’t require you to know the exact size or number of rows in the sheet. It will include all of them.
If you don’t want to include the 1st header row, you could use
The above formula works within the
Transactions sheet. But, if you want to put that formula on a different sheet, you need to add the sheet name reference such as:
If you insert a column or move columns around within the sheet, the formula still works because Google Sheets automatically adapts cell range references.
However, if you start using a new
Transactions sheet where the columns were in different locations or wanted to share the sheet with someone using a different columns order, the formula would no longer work.
Because of fixed range references break in new sheets, the dynamic method is recommended when sharing formulas and sheets with others.
Dynamic Column References
The dynamic method to column references works no matter where the columns are located.
Tiller’s most important “core data” sheets— such as the
Accounts— use the column name in the top row (the header) to identify the column for dynamic references. By searching for the location of the column header name in row 1, we can determine where every column is located.
Use the MATCH Function to Find the Column Header
Use a formula like this to find the location of the Date Column in the
MATCH uses the following format:
=MATCH(search_key, range, [search_type])
In our example, the
MATCH() function uses “Date” as the search key and looks in the range Transactions!$1:$1.
Transactions!$1:$1 is a handy way to define the first, top row in a sheet, no matter how many columns it has.
Use 0 for the search type, which is required when the range is not sorted. Column headers in Tiller sheets are not sorted.
The result of the formula will be a number. 1 for Column A, 2 for Column B etc.
The column number can be useful to determine the lowest and highest numbered column to get a column range using the MIN() and MAX() formulas.
CHAR() Function to get the Column Letter
Getting the column number helps get the column letter.
The column number can be converted to a column letter using the formula:
=CHAR(64 + column_number_here)
In the Unicode table, the letter A is 65. So, 64 + 1 (column #) is 65, which becomes A using the CHAR() function.
Warning: If a Google Sheet has more than 26 columns, the next column will be called AA. The CHAR() formula only works for Columns A (unicode 65) to Z (unicode 90). Most Tiller sheets— especially “core sheets”— have fewer than 26 columns, so this usually won’t be an issue. But if you think a sheet might one day have more than 26 columns, you should use a formula like this:
=IF (column_number_here > 26, "A"&CHAR(64 + column_number_here - 26), CHAR(64 + column_number_here))
If the column number is 27, this will correctly return AA for the column letter.
(64 + column_number_here - 26), you could use
(38 + column_number_here), but the first way makes it easier to understand if you need to troubleshoot the code.
If you don’t need to use the column number, you can get the column letter directly in one formula such as:
This formula works for any sheet, so change
Categories to search the
Categories sheet columns.
Sometimes it is useful to have a way to reference the entire range of cells in a column in a formula. You can create this value in a cell using this formula:
If the column letter B was in
K3, then the cell would become
Transactions!B:B. That will refer to all the cells in Column B.
& symbol in the formula lets you join text strings, such as
"Transactions!" with cell references,
K3, to return a value for the cell.
INDIRECT() Function to Insert the Dynamic Range
Remember the fixed formula we used above using fixed column references:
Now, we can make that formula dynamic by using the text-string reference you created. Assume that using the methods we used above, we have the text value
"Transactions!D:D" in cell
"Transactions!E:E in cell
You can’t just use the following to get the correct result:
=SUMIF(L2,"Food",L3) (this won’t work!)
Instead, you need to use the
INDIRECT() function which returns a cell reference indicated by a string.
This is the correct dynamic formula:
Now the formula will work in any
Transactions sheet no matter the order of the columns.
QUERY() Function works Differently
There is one exception though. If you use column letters in a QUERY() function, because the column references are stored in a text string, the letters won’t dynamically change. We will explain how to solve that problem in a future post.
One best practice is to put a section in your spreadsheet that calculates these values, such as:
Here’s a view of the formulas for the same section:
These columns can be placed to the right of your main views for the table or chart you want your sheet to show. These columns can then be hidden once your sheet is working. They will perform the calculations you need in the background and don’t need to be shown.
Another approach is to make a separate sheet that does all of your sheet reference calculations. Then you refer to this sheet for all your formula calculations.
Either approach works.