Introduction to named ranges
By assigning a name to a range of cells, you can make your formulas much easier to understand and maintain.
For example, instead of writing a formula like this
=SUM('Report Data'!C11:Z11),
you could use a Named Range to write it more intuitively like this
=SUM(MonthlySubtotals).
You can find all of your named ranges in the name box, in the top-left corner, next to the formula bar.
Check out Google's guide to learn how you can add your own named ranges. The rest of this guide will focus on a set of named ranges automatically managed by LiveFlow.
Zero-maintenance named ranges, managed by LiveFlow
When you create a report with LiveFlow, we'll automatically assign names to the six ranges described below, and whenever you refresh a report, we'll automatically update the ranges to reflect any rows or columns added to the report.
Benefits
No matter what changes you make to a report, you can always rely on these named ranges to remain intact. You can change the dates, the columns, or even the company of the report, and any formulas where you've referenced these named ranges will continue to work exactly as expected
The names of these names ranges will always follow the same convention, so you can reference LiveFlow reports in your own sheets without needing to go back and look at the underlying report
When you change the sheet name, we'll update the names of these named ranges to reflect the new sheet name, and we'll update any references to these named ranges with the new names too
{SheetName}_Report, e.g. ProfitLoss_Report
This range captures the whole area of the report, and would be useful for lookup formulas like VLOOKUP and database formulas like QUERY and DSUM.
{SheetName}_Info, e.g. ProfitLoss_Info
This range captures the information describing the report, and might be used with lookup formulas like VLOOKUP if you want to reference any information describing the report.
{SheetName}_UpdatedAt, e.g. ProfitLoss_UpdatedAt
This range points at a single cell, representing the date and time the report was last refreshed.
{SheetName}_RowLabels, e.g. ProfitLoss_RowLabels
This range will cover the row labels listed in the first column of the report, starting in the first row below the column labels. It is especially useful in lookup formulas using VLOOKUP or INDEX/MATCH, where it is often used in combination with {SheetName}_ColumnLabels and/or {SheetName}_Contents.
{SheetName}_ColumnLabels, e.g. ProfitLoss_ColumnLabels
This range will cover the column labels listed in the first row of the report, starting in the first content column (typically column C). It is especially useful in lookup formulas using VLOOKUP or INDEX/MATCH, where it is often used in combination with {SheetName}_RowLabels and/or {SheetName}_Contents.
{SheetName}_Contents, e.g. ProfitLoss_Contents
This range will cover the actual contents of the report, as bounded by the {SheetName}_RowLabels and {SheetName}_ColumnLabels ranges described above. It is especially useful in lookup formulas using XLOOKUP or INDEX/MATCH, where it is often used in combination with {SheetName}_ColumnLabels.
Naming Convention
Each of the six named ranges we manage in your reports will share the same predictable prefix
The prefix will be the same as the sheet name, but we'll remove any characters that are not either a letter or a number
The convention has one caveat: if your sheet name starts with a number, we need to prefix the name of each range with an underscore
Sheet name | Named range prefix | Example |
Profit & Loss |
|
|
Profit & Loss (1) |
|
|
Balance Sheet 2023 |
|
|
2023 Balance Sheet |
|
|