How to refer to a range in another sheet

If you use multiple worksheets in a spreadsheet, then you can refer to an individual cell in a worksheet - including one that you are not currently working in - like this:

=Sheet2!B1 

(A formula like this returns the value in Worksheet Sheet2, Cell B1.)

And you can even combine values from different sheets by putting different sheet-name references into a formula like this;

=Sheet2!B1 + Sheet3!B1

(A formula like this returns the value in Worksheet Sheet2, Cell B1 plus the value in Worksheet Sheet3, Cell B1)

This makes some people think that they should refer to a range of cells in a different worksheet (eg B1:b16) like this:

=SUM(Sheet2!B1:Sheet2!B16)

However using this formula shows #ERROR! as a result, with a not-very-helpful error message of "Formula Parse Error".

The correct way to refer to a range of cells in a separate worksheet is to use the sheet-name only once, like this:

=SUM(Sheet2!B1:B16)
(A formula like this returns the sum of values in cells B1:B16 in Sheet2.)

The reason for this is that within the one function call (eg SUM(...) or AVERAGE(...) ) all the cells must come from the same worksheet. It does not make any sense to say:

=SUM(Sheet2!B1:Sheet3!B16)

because it's not defined what is in-between Sheet2 and Sheet3.

But it does make sense to combine function calls like this:

=SUM(Sheet2!B1:B16) + =SUM(Sheet3!B1:B37) 

(A formula like this returns the sum of values in cells B1:B16 in Sheet2, plus values in cells B1:B37 in Sheet3)

Extra for Experts

Of course this only works if they type of function that you are using is distributitive, ie you can do one part and the other part, and then you put them both together using the same functon, eg

= MIN ( MIN(Sheet2!B1:B16), MIN(Sheet3!B1:B22))
(A formula like this returns the smallest value in cells B1:B16 in Sheett and cells B1:B37 in Sheet3)

For functions like AVERAGE, where the number of items in the underlying range is used in the calculation, it does not work, eg

= AVERAGE ( AVERAGE(Sheet2!B1:B16), AVERAGE(Sheet3!B1:B22)) 

will give a result but it will generally not be the arithmetic mean of the value in cells B1:B16 in Sheett and cells B1:B37 in Sheet3 because the values from Sheet2 will get too great a weighting in the calculation.

Was this helpful?  You might also like: 

• Understanding spreadsheets, sheets and worksheets
• Where to get help with Google Sheets