Challenge - 5 Problems
Worksheet Reference Master
Get all challenges correct to earn this badge!
Test your skills under time pressure!
📊 Formula Result
intermediate2:00remaining
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
What value will appear in Sheet1 cell B1?
In Sheet1 cell B1, the formula
=Sheet2!A1 + 20 is entered.What value will appear in Sheet1 cell B1?
Attempts:
2 left
💡 Hint
Remember that referencing another sheet uses the sheet name followed by an exclamation mark and the cell address.
✗ Incorrect
The formula adds 20 to the value in Sheet2 cell A1, which is 50. So, 50 + 20 = 70.
❓ Function Choice
intermediate2:00remaining
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?
Which formula will do this correctly?
Attempts:
2 left
💡 Hint
Use single quotes around sheet names when they contain spaces or special characters. For simple sheet names without spaces, quotes are optional.
✗ Incorrect
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
advanced2:00remaining
What error does this formula produce?
In a workbook, you have a sheet named 2023 Sales.
You enter the formula
What error will Excel show?
You enter the formula
=2023 Sales!B2 * 2 in another sheet.What error will Excel show?
Attempts:
2 left
💡 Hint
Sheet names with spaces must be enclosed in single quotes.
✗ Incorrect
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
advanced2:00remaining
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?
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?
Attempts:
2 left
💡 Hint
Use INDIRECT with single quotes around sheet names when they might have spaces or special characters.
✗ Incorrect
Option A correctly builds the reference with single quotes around the sheet name and the cell address, making it dynamic and safe.
❓ data_analysis
expert3:00remaining
How many cells are summed by this formula?
In a workbook, you have a sheet named Sales Data.
The formula
How many individual cells does this formula add together?
The formula
=SUM('Sales Data'!A1:C3) is entered in another sheet.How many individual cells does this formula add together?
Attempts:
2 left
💡 Hint
Count the number of rows times the number of columns in the range.
✗ Incorrect
The range A1:C3 covers 3 columns (A, B, C) and 3 rows (1, 2, 3), so 3 x 3 = 9 cells.