Referencing other worksheets in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this formula referencing another sheet?

Given a workbook with two sheets: Sheet1 and Sheet2. Cell A1 in Sheet2 contains the number 50.

In Sheet1 cell B1, the formula =Sheet2!A1 + 20 is entered.

What value will appear in Sheet1 cell B1?

A 50

B 70

C #REF!

D 20

💡 Hint

Remember that referencing another sheet uses the sheet name followed by an exclamation mark and the cell address.

The formula adds 20 to the value in Sheet2 cell A1, which is 50. So, 50 + 20 = 70.

❓ Function Choice

intermediate

Which formula correctly sums values from another worksheet?

You want to sum the range A1:A5 from a worksheet named Data into your current sheet.

Which formula will do this correctly?

A =SUM('Data'!A1:A5)

B =SUM(Data!A1:A5)

C =SUM(Data!A1;A5)

D =SUM(Data!A1-A5)

💡 Hint

Use single quotes around sheet names when they contain spaces or special characters. For simple sheet names without spaces, quotes are optional.

Both options A and B correctly sum the range A1:A5 from the 'Data' sheet. Option A uses single quotes which are required if the sheet name contains spaces or special characters. Option B works because 'Data' is a simple sheet name without spaces.

📊 Formula Result

advanced

What error does this formula produce?

In a workbook, you have a sheet named 2023 Sales.

You enter the formula =2023 Sales!B2 * 2 in another sheet.

What error will Excel show?

A #VALUE!

B #REF!

C #NAME?

D No error, correct calculation

💡 Hint

Sheet names with spaces must be enclosed in single quotes.

Because the sheet name contains a space and is not enclosed in single quotes, Excel treats '2023' as a number and 'Sales' as an unknown name, causing a #NAME? error.

🎯 Scenario

advanced

Referencing a cell from a dynamically named sheet

You have sheets named Jan, Feb, and Mar.

In cell A1 of your summary sheet, you type the month name (e.g., "Feb").

You want to get the value from cell B2 of the sheet named in A1.

Which formula will correctly do this?

A =INDIRECT("'" & A1 & "'!B2")

B =A1!B2

C =INDIRECT("B2" & A1)

D =INDIRECT(A1 & "!B2")

💡 Hint

Use INDIRECT with single quotes around sheet names when they might have spaces or special characters.

Option A correctly builds the reference with single quotes around the sheet name and the cell address, making it dynamic and safe.

❓ data_analysis

expert

How many cells are summed by this formula?

In a workbook, you have a sheet named Sales Data.

The formula =SUM('Sales Data'!A1:C3) is entered in another sheet.

How many individual cells does this formula add together?

A 9

B 3

C 6

D 12

💡 Hint

Count the number of rows times the number of columns in the range.

The range A1:C3 covers 3 columns (A, B, C) and 3 rows (1, 2, 3), so 3 x 3 = 9 cells.

Practice

1. Which of the following is the correct way to reference cell A1 from a worksheet named Sales in another worksheet?

easy

A. =Sales:A1

B. =A1!Sales

C. =Sales!A1

D. =Sheet1.A1

Solution

1. Step 1: Understand worksheet referencing syntax

   In Excel, to reference a cell from another worksheet, use SheetName!CellAddress.

2. Step 2: Apply the syntax to the given worksheet and cell

   For worksheet Sales and cell A1, the correct reference is =Sales!A1.

3. Final Answer:

   =Sales!A1 -> Option C

4. Quick Check:

   SheetName!Cell = =Sales!A1 [OK]

Hint: Use SheetName!Cell to reference another sheet [OK]

Common Mistakes:

• Putting cell before sheet name
• Using colon instead of exclamation mark
• Using dot notation like Sheet1.A1

2. Which formula correctly references cell B2 from a worksheet named Annual Report (note the space in the name)?

easy

A. ='Annual Report'!B2

B. =AnnualReport!B2

C. =Annual_Report!B2

D. =Annual Report!B2

Solution

1. Step 1: Recognize the need for quotes with spaces in sheet names

   When a worksheet name contains spaces, it must be enclosed in single quotes in formulas.

2. Step 2: Apply correct syntax for referencing cell B2

   The correct reference is ='Annual Report'!B2 with single quotes around the sheet name.

3. Final Answer:

   ='Annual Report'!B2 -> Option A

4. Quick Check:

   Sheet names with spaces need quotes [OK]

Hint: Put single quotes around sheet names with spaces [OK]

Common Mistakes:

• Omitting quotes around sheet names with spaces
• Replacing spaces with underscores incorrectly
• Using double quotes instead of single quotes

3. Given two worksheets: Data and Summary. Cell A1 in Data contains the number 10. What will be the result in cell B1 of Summary if the formula =Data!A1 * 2 is entered?

medium

A. 10

B. 20

C. #REF!

D. Data!A1 * 2

Solution

1. Step 1: Identify the value in Data!A1

   Cell A1 in the Data sheet contains the number 10.

2. Step 2: Calculate the formula in Summary!B1

   The formula multiplies the value from Data!A1 by 2, so 10 * 2 = 20.

3. Final Answer:

   20 -> Option B

4. Quick Check:

   10 * 2 = 20 [OK]

Hint: Multiply referenced cell value directly [OK]

Common Mistakes:

• Expecting the formula text as output
• Getting #REF! error due to wrong sheet name
• Not multiplying the value, just copying it

4. You want to reference cell C3 from a worksheet named 2023 Sales, but your formula =2023 Sales!C3 returns a #NAME? error. What is the correct fix?

medium

A. Change formula to =2023Sales!C3

B. Change formula to =2023 Sales C3

C. Change formula to =2023_Sales!C3

D. Change formula to ='2023 Sales'!C3

Solution

1. Step 1: Identify cause of #NAME? error

   The sheet name contains a space, so Excel treats 2023 as a number and Sales as an unknown name, causing the error.

2. Step 2: Fix formula by adding single quotes around sheet name

   Enclose the sheet name with spaces in single quotes: ='2023 Sales'!C3.

3. Final Answer:

   ='2023 Sales'!C3 -> Option D

4. Quick Check:

   Sheet names with spaces need quotes [OK]

Hint: Add single quotes around sheet names with spaces [OK]

Common Mistakes:

• Removing spaces without quotes
• Using underscores instead of spaces
• Omitting quotes causing #NAME? error

5. You have a workbook with sheets named Jan, Feb, and Mar. Each sheet has sales data in cell B5. In a summary sheet, which formula correctly sums the sales from all three months?

hard

A. =SUM(Jan:Mar!B5)

B. =Jan!B5 + Feb!B5 + Mar

C. =SUM('Jan:Mar'!B5)

D. =SUM(Jan!B5:Mar!B5)

Solution

1. Step 1: Understand 3D referencing in Excel

   Excel allows summing the same cell across multiple sheets using Sheet1:Sheet3!Cell syntax.

2. Step 2: Apply 3D sum for sheets Jan to Mar

   The formula =SUM(Jan:Mar!B5) sums cell B5 across all sheets from Jan through Mar.

3. Final Answer:

   =SUM(Jan:Mar!B5) -> Option A

4. Quick Check:

   3D sum uses Sheet1:SheetN!Cell [OK]

Hint: Use SUM(Sheet1:SheetN!Cell) to sum across sheets [OK]

Common Mistakes:

• Trying to sum with plus signs for many sheets
• Putting sheet names in quotes with colon
• Using range notation for cells across sheets incorrectly