Home > Formulas > Lookup > Compare Cells Check Cell Contents With INDIRECT Function Use the Excel INDIRECT function to compare cell values, and combine it with the FORMULATEXT function to compare formulas on multiple sheets. Thank you to Rudy from Minneapolis, for sending his example of this technique.

Introduction: Compare Values and Formulas

In this example, there are 3 sheets with regional data -- East, Central and West. All 3 sheets have the same structure, with totals in cell B8.

If a workbook has multiple sheets with identical structures, you can use links, and 3-D formulas, to create sums from all the sheets. When doing this, it is important that the structures do not change on the individual sheets, in order to keep the totals accurate.

In this tutorial, you'll see how to create a 3-D formula, and set up a verification table, to check that the cell values and formulas are identical on all three sheets.

Create a 3-D Formula

On the Summary sheet, there is a 3-D formula in cell C6, that calculates the sum of the values in cell B8, on all three sheets.

To create a 3-D Sum:

• Select the cell where you want the total
• Type the start of the formula: =SUM(
• Click on the tab for the East sheet
• Press the Shift key, and click on the tab for the West sheet -- this selects those two sheets, and any sheets between them.
• Click on the cell that contains the total -- B8
• Type a closing bracket, then press Enter, to complete the formula

The completed formula is shown in the screen shot below: =SUM(East:West!B8)

Problem With Simple Links

A simple way to check the cells on multiple sheets is to link to those cells, from a verification sheet. However, this can give the false impression that everything is okay, even if the structure on one or more sheets have changed.

To see an example of this problem, links to the 3 sheets have been set up on the CheckValuesLink sheet. The values are the same for each sheet, so everything looks okay.

Then, insert a blank row on the Central sheet, above row 6. The total is shifted down to row 9.

On the Summary sheet, the Region Total is now incorrect -- it shows 29 instead of 48 -- because the Central total is not included.

However, the links on the CheckValuesLink sheet continue to show the same values on the Central sheet, even though the structure has changed. A closer look shows that the links have shifted, to show the new location of the linked cells on the Central sheet

 Creating links to the Region sheets was quick and easy, but is not a reliable method for verifying that the sheet structures have not been changed. 

Check Values With INDIRECT

Instead of using simple links to the Region sheets, the INDIRECT function can be used to create a static link to a specific sheet and cell.

NOTE: This technique is best for checking a small number of key cells, because the INDIRECT function is volatile, and could slow down the workbook calculation, if used in a large number of cells. (A volatile function recalculates every time that Excel calculates -- even if the cells it references have not changed. Read more about volatile Excel functions on Charles Williams' website.)

To check the values, follow these steps:

• Make a copy of the completed Check Cell Values sheet, and change the heading to Compare Cell Values
• Clear the formulas from the grid cells

Create a Text String

The INDIRECT function returns a reference to a range, based on a text string, so the first step in building this formula is to create a text string, with the sheet name from column B, and cell address from row 4:

• In cell C5, type this formula:  ="'" & $B5 & "'!" & C$4
  • The reference to cell $B5 has the column locked ($B), so it will not change when the formula is copied across the row.
  • The reference to cell C$4 has the row locked ($4), so it will not change when the formula is copied down the column.
• Press Enter, to see the result

In cell C5, the formula result combines the sheet name in cell B5 with the cell address in cell C4 -- 'East'!A4

• The sheet name is enclosed with single quote marks, to prevent problems if a sheet name contains a space character
• The ! marks the end of the sheet name

Add the INDIRECT Function

Next, revise the text string formula, to wrap it with the INDIRECT function. This will create a reference to cell A4 on the East sheet.

• In cell C5, revise the formula:  =INDIRECT("'" & $B5 & "'!" & C$4)
• Press Enter, to see the result

The text string in cell C5 is 'East'!A4, and the INDIRECT function returns a reference to that range, so cell C5 shows the value that is in cell A4, on the East sheet

To confirm that the result is correct, check cell A4 on the East sheet. That cell contains "A", which matches the result of the formula in cell C5.

Complete the Grid

To complete the cell checking grid, copy the completed formula to the other cells in the grid:

• Select cell C5
• Drag the fill handle to the right, to fill the formula across to cell F5
• Then, drag the fill handle down, to fill the formula down to row 7

The values from cells A4:A7 on all 3 region sheets are now visible, and can be easily compared for structural changes. In the screen shot below, all the regions have the same values in each cell, so the structures are identical.

Test the Cell Checking Grid

To test the grid results, change the structure on one sheet:

• Insert a blank row on the Central sheet, above row 6.
• Check the grid, and it shows that the Central values are different from the other sheets.

To restore the sheet structure:

• Delete the blank row that was inserted on the Central sheet, above row 6.
• Check the grid, and it shows that the Central values are now the same as the other sheets.

Check Formulas With INDIRECT and FORMULATEXT

In addition to checking cell values, the INDIRECT function can be combined with the FORMULATEXT function, to check the formula in a specific sheet and cell.

• The FORMULATEXT function is available in Excel 2013, and later versions
• This technique is best for checking a small number of key cells, because both the INDIRECT and FORMULATEXT functions are volatile, and could slow down the workbook calculation, if used in a large number of cells.

To check the formulas, follow these steps:

• Make a copy of the completed Check Cell Values sheet, and change the heading to Compare Cell Formulas
• Change the cell addresses in the headings, to identify cells that might contain formulas. In this example, two columns were deleted, and the cell addresses were changed to B7 and B8

Now, the grid shows the values from cells B7 and B8 on each region's sheet.

Add the FORUMULATEXT Function

To show the cell formulas, instead of values, revise the formula, to wrap it with the FORMULATEXT function.

• In cell C5, revise the formula:  =FORMULATEXT(INDIRECT("'" & $B5 & "'!" & C$4))
• Press Enter, to see the result

If the referenced cell does not contain a formula, the result will be #N/A, as in cell C5, below.

Add the IFERROR Function

If you want to hide any errors in the formula results, add the IFERROR function. The IFERROR function has 2 arguments:

• value -- what to check for an error
• value_if_error -- what to show if the value is an error (two hyphens "--")

In this example,

• the value arguement is the FORMULATEXT function result
• the value_if_error argument will be two hyphens "--" (you could use an empty string instead (""), to make the cell appear empty

To hide the errors:

• In cell C5, revise the formula:  =IFERROR(FORMULATEXT(INDIRECT("'" & $B5 & "'!" & C$4)),"--")
• Press Enter, to see the result

Now, if the referenced cell does not contain a formula, the FORMULATEXT result will be #N/A, and the IFERROR result is 2 hyphens, as in cell C5, below.

Complete the Grid

To complete the cell checking grid, copy the completed formula to the remaining cells:

• Select cell C5
• Drag the fill handle to the right, to fill the formula across to cell D5
• Then, drag the fill handle down, to fill the formula down to row 7

The formulas from cells B7:B8 on all 3 region sheets are now visible, and can be easily compared for structural changes. In the screen shot below, all the regions have the same formulas in each cell, so the structures are identical.

Test the Cell Checking Grid

To test the grid results, change the structure on one sheet:

• Insert a blank row on the Central sheet, above row 6.
• Check the grid, and it shows that the Central formulas are different from the other sheets.

To restore the sheet structure:

• Delete the blank row that was inserted on the Central sheet, above row 6.
• Check the grid, and it shows that the Central formulas are now the same as the other sheets.

Download the Sample File

To see how the INDIRECT and FORMULATEXT functions work to check values and formulas, download the SheetCompare sample workbook. The zipped file is in xlsx format, and does not contain macros. The CheckValues and CheckFormulas sheets have conditional formatting added, to highlight any differences in the sheet structures.

NOTE: The FORMULATEXT function is available in Excel 2013 and later.

Related Tutorials

Compare Cell Values

VLOOKUP Troubleshooting

INDEX / MATCH Functions

INDIRECT function

EXACT Function Examples