Formula Errors

Evaluate Values Across Excel Worksheets Without a 3D Array Formula

Chanuka GeekiyanageChanuka Geekiyanage Nov 14, 2026 868 views

Question details

Evaluate individual cells across multiple worksheets and return a result for each cell.

Evaluate Values Across Excel Worksheets Without a 3D Array Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to test cells A1:A2 on several worksheets and return "t" or "f" for each value.
Observed behavior
The IF formula using a three-dimensional worksheet reference returns #REF! instead of evaluating each cell individually.
Before you start

Identify the worksheets and cell ranges you need to evaluate. Decide whether you want individual "t" or "f" results or an aggregate count, since these require different formulas.

Solution 1Recommended

Evaluate each cell with a helper column on each worksheet

Use helper columns for a straightforward, maintainable result on each source worksheet.

Excel does not evaluate a three-dimensional reference cell by cell in the proposed IF formula. A helper formula evaluates each worksheet's cells separately.

1
Enter the first formula

On Sheet1, select B1 and enter =IF(A1=1,"t","f").

2
Fill the range

Drag the fill handle from B1 to B2 to test A2. Extend the formula down if you have more source rows.

3
Repeat on other sheets

On Sheet2 and each other source worksheet, enter the same formula in B1 and fill it down alongside the values in column A.

4
Review the results

Check column B on each worksheet. Each cell returns "t" when the corresponding value in column A equals 1 and "f" otherwise.

Evaluate each cell with a helper column on each worksheet
Combining results: If you need a count or another aggregate rather than individual labels, use functions such as SUMIF or SUMPRODUCT on the prepared worksheet ranges and combine the results.
Work with spreadsheet formulas in WPS

Evaluate worksheet values in WPS Spreadsheet

Use helper formulas in WPS Spreadsheet to test cells on each worksheet and return an individual result for every value.

  1. 1. Open the workbook: Open WPS Spreadsheet, select Open, and choose your Excel workbook.
  2. 2. Add a helper formula: On the first source worksheet, select B1 and enter =IF(A1=1,"t","f").
  3. 3. Fill the formula: Drag the fill handle from B1 down alongside the values you want to test.
  4. 4. Repeat and save: Repeat the formula on the other source worksheets, then select File > Save As to save the workbook.
Evaluate cells individually with familiar IF formulasOpen and edit Microsoft Excel XLSX and XLS filesKeep results easy to review with worksheet helper columnsUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel 3D IF formula return #REF!?

Excel does not evaluate Sheet2:Sheet1!A1:A2 as individual cells in the proposed IF formula. Test each worksheet separately with helper formulas or construct individual references with INDIRECT.

Can I show results from several worksheets on one sheet?

Yes. List worksheet names on a results sheet and use INDIRECT to construct a reference to each source cell. Check that the listed names match the worksheet tabs.

How can I count matches instead of returning "t" or "f"?

For Sheet1 and Sheet2, use =SUMPRODUCT(--(Sheet1!A1:A2=1))+SUMPRODUCT(--(Sheet2!A1:A2=1)). This returns a combined count, not an individual result for each cell.

What if a lookup value needs to be transformed first?

Add a helper column on each source worksheet to transform the value, such as with LEFT, and then apply SUMIF or SUMIFS to that helper range.