Cross-sheet references in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of a cross-sheet SUM formula

You have two sheets: Sheet1 and Sheet2. In Sheet1, cells A1 to A3 contain numbers 10, 20, and 30 respectively. In Sheet2, cell B1 contains the formula =SUM(Sheet1!A1:A3). What is the value displayed in Sheet2!B1?

A Syntax Error

B 100

C 60

D 0

💡 Hint

Remember that Sheet1!A1:A3 refers to cells A1 to A3 in Sheet1.

The formula sums the values 10 + 20 + 30 from Sheet1 cells A1 to A3, resulting in 60.

❓ Function Choice

intermediate

Choosing the correct formula for cross-sheet average

You want to calculate the average of values in cells B2 to B6 on a sheet named Data from another sheet. Which formula will correctly do this in your current sheet?

A =AVERAGE('Data'!B2:B6)

B =AVERAGE(Data!B2:B6)

C =AVERAGE(Data!B2-B6)

D =AVERAGE(Data:B2:B6)

💡 Hint

Sheet names with no spaces can be used without quotes, but quotes are safer.

Option A uses correct syntax with quotes around the sheet name, which is required if the sheet name has special characters or spaces. Option A works only if the sheet name has no spaces or special characters, but B is safer and always correct.

🎯 Scenario

advanced

Fixing a broken cross-sheet reference after renaming a sheet

You have a formula in Sheet3 cell C5: =SUM(Sales!C1:C10). You renamed the sheet Sales to 2023 Sales. Now the formula shows a #REF! error. Which formula fixes the error?

A =SUM(Sales!C1:C10)

B =SUM(2023 Sales!C1:C10)

C =SUM('Sales 2023'!C1:C10)

D =SUM('2023 Sales'!C1:C10)

💡 Hint

Sheet names with spaces must be enclosed in single quotes.

After renaming the sheet to '2023 Sales', the formula must use single quotes around the sheet name to handle the space. Option D correctly updates the reference.

❓ data_analysis

advanced

Analyzing cross-sheet reference impact on data update

Sheet1 column A has numbers 1 to 5. Sheet2 cell B1 has formula =SUM(Sheet1!A1:A5). If you change Sheet1!A3 from 3 to 10, what happens to Sheet2!B1?

A The value in Sheet2!B1 updates automatically to 22

B The value in Sheet2!B1 remains the same until you refresh manually

C Sheet2!B1 value becomes 10

D Sheet2!B1 shows an error because of the change

💡 Hint

Formulas recalculate automatically when referenced data changes.

Changing a referenced cell value updates the formula result automatically. The sum changes from 1+2+3+4+5=15 to 1+2+10+4+5=22.

📊 Formula Result

expert

Result of INDIRECT with cross-sheet reference

In Sheet1 cell A1 you have the text Data. In the Data sheet, cells B1:B3 contain numbers 5, 10, 15 respectively. In Sheet3 cell C1 you enter the formula =SUM(INDIRECT(Sheet1!A1 & "!B1:B3")). What is the value in Sheet3!C1?

A Syntax Error

B 30

C 0

D #REF! error

💡 Hint

INDIRECT builds a reference from text. Check what A1 contains and what range it points to.

Sheet1!A1 contains 'Data', so INDIRECT('Data!B1:B3') refers to Sheet named Data cells B1 to B3. The sum of 5 + 10 + 15 is 30.