INDIRECT for dynamic sheet references
IntermediateINDIRECT lets you build a cell reference from text, which means you can use cell values to control where a formula looks. This is a powerful technique for dashboards that need to pull data from different sheets depending on user input.
INDIRECT takes one argument: a text string that looks like a cell or range reference. It converts that text into a live reference the formula can use. For example, if cell A1 contains the text "B5", then =INDIRECT(A1) returns the value of B5, not the word "B5".
This works across sheets, too. The text reference just needs to include the sheet name, like =INDIRECT("Q1!B2").
Your task
The workbook has four quarterly sheets ( Q1 , Q2 , Q3 , Q4 ), each with the same layout: Category , Revenue , and Expenses columns with rows for Marketing, Operations, and Sales.
The Dashboard sheet has a parameter in B1 where you can type a quarter name (currently Q2). Your goal is to build formulas in the Dashboard that pull the correct figures from whichever quarter is selected, so the whole dashboard updates when you change B1.
In cells B4:C6 on the Dashboard, write formulas using INDIRECT to return the revenue and expenses from the selected quarter's sheet. The formulas should reference B1 so they work for any quarter name you enter.
Need some help?
Hint 1
INDIRECT expects a text reference. Build that text by combining the quarter name from B1 with an exclamation mark and a cell address like B2 or C2.
Hint 2
Wrap the combined text in INDIRECT() to turn it into a live cross-sheet reference. Anchor B1 with a dollar sign so the sheet-name part stays fixed when you copy the formula.
INDIRECT for dynamic sheet references
IntermediateINDIRECT lets you build a cell reference from text, which means you can use cell values to control where a formula looks. This is a powerful technique for dashboards that need to pull data from different sheets depending on user input.
INDIRECT takes one argument: a text string that looks like a cell or range reference. It converts that text into a live reference the formula can use. For example, if cell A1 contains the text "B5", then =INDIRECT(A1) returns the value of B5, not the word "B5".
This works across sheets, too. The text reference just needs to include the sheet name, like =INDIRECT("Q1!B2").
Your task
The workbook has four quarterly sheets ( Q1 , Q2 , Q3 , Q4 ), each with the same layout: Category , Revenue , and Expenses columns with rows for Marketing, Operations, and Sales.
The Dashboard sheet has a parameter in B1 where you can type a quarter name (currently Q2). Your goal is to build formulas in the Dashboard that pull the correct figures from whichever quarter is selected, so the whole dashboard updates when you change B1.
In cells B4:C6 on the Dashboard, write formulas using INDIRECT to return the revenue and expenses from the selected quarter's sheet. The formulas should reference B1 so they work for any quarter name you enter.
Need some help?
Hint 1
INDIRECT expects a text reference. Build that text by combining the quarter name from B1 with an exclamation mark and a cell address like B2 or C2.
Hint 2
Wrap the combined text in INDIRECT() to turn it into a live cross-sheet reference. Anchor B1 with a dollar sign so the sheet-name part stays fixed when you copy the formula.