Function Problems

Use an Excel Formula to Reference Different Worksheets Dynamically

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 868 views

Question details

Calculate a result from different worksheets based on a worksheet name or category entered in a cell.

Use an Excel Formula to Reference Different Worksheets Dynamically
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.
Before you start

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.

Solution 1Recommended

Use a Nested IF Formula for Specific Worksheets

Use this approach when each worksheet requires a reference to a different cell.

1
Enter a worksheet name

On worksheet3, enter worksheet1 or worksheet2 in cell B3.

2
Enter the formula

Select a result cell such as C3 and enter =IF(B3="worksheet1",worksheet3!J2-worksheet1!K2,IF(B3="worksheet2",worksheet3!J2-worksheet2!I2,""))

3
Test both choices

Change B3 between worksheet1 and worksheet2. The formula subtracts worksheet1!K2 or worksheet2!I2 from worksheet3!J2, respectively.

Use a Nested IF Formula for Specific Worksheets
Unmatched names: The formula returns an empty string if B3 matches neither name. Replace the final "" with a message if you want to flag other entries.
Work with formulas in WPS Office

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. 1. Open the workbook: In WPS Spreadsheets, select Open and choose your workbook.
  2. 2. Enter the selector: On worksheet3, enter worksheet1 or worksheet2 in B3.
  3. 3. Add the formula: Select C3 and enter =IF(B3="worksheet1",worksheet3!J2-worksheet1!K2,IF(B3="worksheet2",worksheet3!J2-worksheet2!I2,""))
  4. 4. Test and save: Change B3 to test each result, then use Menu > Save As to save the workbook as an XLSX file.
Select worksheet values dynamically with familiar formulasOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

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.