Some people recommend using Apps Script to include an entire sheet in a formula as a reference in Google Sheets. But that is not necessary.
We can include an entire sheet in formulas two ways in Google Sheets.
I’ll try to explain both the solutions (a range reference that partially justifies the entire sheet and another dynamic range reference that fully justifies the entire sheet ).
What do I mean by the entire sheet in a formula then?
It’s referring to infinite rows and columns (open rows and columns) as an array in formulas.
Entire Sheet – Reference that Partially Justifies
As a normal practice, you can include ‘an entire sheet’ as a reference in a formula as below.
I am referring to the first 50,000 rows in ‘Sheet1’ within a formula in ‘Sheet2’.
=ArrayFormula(Sheet1!A1:50000)This will copy all the contents from ‘Sheet1’ (50000 rows only) to ‘Sheet2’.
See how I have included the entire ‘Sheet1’ range in the formula (you can change 50000 with the last row in your ‘Sheet1’).
Normally we specify an array/range in formulas like A1:Z10. In this A1 is the address of the very first cell and Z10 is the address of the last cell in the array.
The colons (:) separates both the cell addresses. Needless to say, A and Z are column letters and 1 and 10 are row numbers.
If I omit the column letter from the last cell address Z10, the reference would look like A1:10. Since there is no column letter, such a reference treats the last column as infinite.
That means the array reference is from the first cell in the sheet to the 10th row; a total of 10 rows.
If you put column letter Z, then the array would be limited to the 10th row in column Z.
Based on the number of rows in your sheet, you can easily specify an entire sheet, like A1:50000 in a formula.
In the future, if you add more rows, then you may need to modify the formula. So if there is a dynamic way to specify or include an entire sheet in a formula as a reference?
Dynamic Way to Include an Entire Sheet as Reference in Google Sheets
First things first! In order to specify an entire sheet in any formula, you must not enter the formula in the referenced sheet.
For example, I want to include the entire sheet A in a formula. If I enter the formula in sheet A itself, it would cause a circular dependency error. So the formula must be in another tab, for example, sheet B.
I have already explained why the above (earlier) reference is not dynamic. It is because the reference is limited to 50,000 rows.
How to solve this?
Replace 50,000 with the count of rows in the referenced sheet.
For example instead of 50,000 we can specify rows(A1:A). The ROWS will return the number of rows in the included range.
Since A1:A is an open range, the Rows formula will return the count of all the rows – present and future.
That doesn’t mean you can include en entire sheet in formulas in Google Sheets as =ArrayFormula(Sheet1!A1:rows(Sheet1!A1:A))
You should include INDIRECT together (you can omit ArrayFormula).
Dynamic Entire Sheet Reference in a Formula:
=indirect("Sheet1!A1:"&rows(Sheet1!A1:A))Now let’s see how to include the entire ‘Sheet1’ in a few formulas including SORT, COUNTIF, FILTER, and QUERY.
The above formula is in Sheet2!A1, which copies the entire ‘Sheet1’. I am switching between ‘Sheet2’ and ‘Sheet1’ to show you the contents in both. The cell A1 in Sheet2 has the formula.
Specify an Entire Sheet as Reference in a Few Different Functions
Let’s start this example section with the Sort function in an entire sheet range.
Include an Entire Sheet as Reference in SORT in Google Sheets
Want to sort an entire sheet from another tab? Then this SORT with dynamic ‘entire’ range will come in handy.
=sort(indirect("Sheet1!A1:"&rows(Sheet1!A1:A)))In this Sort, I didn’t specify any column number to sort. So the formula considers the column 1 as the sort column and sort order as A-Z.
In this type of formula, you can include sort column as well as sort order as usual.
Just for example;
=sort(indirect("Sheet1!A1:"&rows(Sheet1!A1:A)),2,false)Specify an Entire Sheet in Countif in Google Sheets:
The following Countif will count the string “apple” in ‘every nook and corner’ (all cells) of ‘Sheet1’.
=countif(indirect("Sheet1!A1:"&rows(Sheet1!A1:A)),"apple")Specify an Entire Sheet in Filter in Google Sheets:
To Filter an entire Sheet based on a value in column A, use the below formula.
=filter(indirect("Sheet1!A1:"&rows(Sheet1!A1:A)),Sheet1!A1:A="apple")I have included an entire sheet in the above filter formula as a reference. The criterion is the string “apple” in column A.
So the formula would filter all the rows containing “apple” in the first column of the range.
Include an Entire Sheet in Query in Google Sheets:
I am simply rewriting the above Filter with Query. That means the criterion is the same.
=query(indirect("Sheet1!A1:"&rows(Sheet1!A1:A)),"Select * where A='apple'")I hope I have included everything in this post that you want to know about how to include an entire sheet as a reference in Google Sheets formulas.
That’s all. Enjoy!
