Being somewhat new to Sheets, I am trying to decipher some of the formulas used in Tiller. Is there a tutorial on formulas for Sheets anywhere? I never got into developing complex spreadsheets in Xcell either so there will not be much transference from there in trying to understand Sheets better. Should I start with a search in Google?
Hi @yogiwan :
Great question! The Tiller Community has some excellent places to begin. Using the search tool, look for “Tutorial,” or “Formula” or other terms like that. For a start, try these:
Sometimes our lookups need to evaluate both column and row headers to find the appropriate value.
In fact, this situation came up in the new Budget Builder template… The budget builder creates a month-by-month budget, but the sorted category order (likely) won’t match the Categories sheet. To move the values to the appropriate budget month in the Categories sheet required a two-dimensional lookup that matched the category name and also the budget month before pulling a value into the Categorie…
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:
Method
Fixed
Dynamic
How To Use
Use the Column Letter
Use MATCH, CHAR and INDIRECT functions
Example of usage
=Transactions!A2:A
=INDIRECT…
One feature in Google Sheets that I find incredibly helpful that isn’t well understood is the ability to build custom arrays/ranges within formulas. The Google Sheets documentation has a brief & unassuming callout to this capability that does not really acknowledge its power or potential.
Let’s dig into how this capability works and how to use it to super charge your spreadsheets…
documentation above shows some pretty simple examples.
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 …
For those that like to create their own sheets and formulas, you may have run into a stumbling block to using your monthly category budget values.
Since the budget for each month is in a separate monthly column, this can make writing formulas to work with this data a bit complicated.
One solution is to create a sheet that “flattens” this data, so all the information is in just 3 columns: Category, Month, and Budget.
@benlcollins latest weekly newsletter points out the FLATTEN function is now …
jono
January 7, 2022, 2:51am
3
Hi @yogiwan ,https://support.google.com/docs/table/25273
I’d also recommend Ben Collins’ website:
Beginner to advanced tutorials covering Google Sheets, Apps Script, data analysis, automation and APIs. Learn new skills today!
Est. reading time: 10 minutes
Jon