Use an Excel Formula to Reference Different Worksheets Dynamically
Question details
Calculate a result from different worksheets based on a worksheet name or category entered in a cell.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A cell identifies which worksheet should supply a value for a calculation.
- Observed behavior
- The formula should select the appropriate worksheet and cell based on the entered name.
Check the worksheet names and the cells used in each calculation. The examples assume the selector is in B3 on worksheet3, and that worksheet3!J2 contains the value to calculate from.
Use a Nested IF Formula for Specific Worksheets
Use this approach when each worksheet requires a reference to a different cell.
On worksheet3, enter worksheet1 or worksheet2 in cell B3.
Select a result cell such as C3 and enter =IF(B3="worksheet1",worksheet3!J2-worksheet1!K2,IF(B3="worksheet2",worksheet3!J2-worksheet2!I2,""))
Change B3 between worksheet1 and worksheet2. The formula subtracts worksheet1!K2 or worksheet2!I2 from worksheet3!J2, respectively.

Use INDIRECT for the Same Cell on Different Worksheets
Use INDIRECT when several worksheets have the same layout and the same cell address supplies the value.
Create Dynamic Worksheet References in WPS Spreadsheets
Use IF or INDIRECT in WPS Spreadsheets to calculate from a worksheet selected in a cell, then save the workbook in Excel format.
- 1. Open the workbook: In WPS Spreadsheets, select Open and choose your workbook.
- 2. Enter the selector: On worksheet3, enter worksheet1 or worksheet2 in B3.
- 3. Add the formula: Select C3 and enter =IF(B3="worksheet1",worksheet3!J2-worksheet1!K2,IF(B3="worksheet2",worksheet3!J2-worksheet2!I2,""))
- 4. Test and save: Change B3 to test each result, then use Menu > Save As to save the workbook as an XLSX file.

Frequently Asked Questions
What happens if I enter an invalid worksheet name?
The nested IF example returns an empty string for an unmatched name. INDIRECT returns a #REF! error if the constructed reference does not point to an existing worksheet.
Can I reference worksheet names that contain spaces?
Yes. Enclose the sheet name in single quotes, as in ='Sales East'!K2. The INDIRECT example adds those quotes around the name in B3.
Why might INDIRECT make my workbook slower?
INDIRECT is volatile, so Excel recalculates it whenever recalculation occurs. For a small, fixed set of sheets, nested IF can avoid using INDIRECT.
How do I prevent users from entering the wrong sheet name?
Select B3 and use Data > Data Validation to create a list of permitted worksheet names. Make sure the list matches the names used by your formula.




