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.
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.
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:
The completed formula is shown in the screen shot below: =SUM(East:West!B8)
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.
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:
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, the formula result combines the sheet name in cell B5 with the cell address in cell C4 -- 'East'!A4
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.
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.
To complete the cell checking grid, copy the completed formula to the other cells in the grid:
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.
To test the grid results, change the structure on one sheet:
To restore the sheet structure:
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.
To check the formulas, follow these steps:
Now, the grid shows the values from cells B7 and B8 on each region's sheet.
To show the cell formulas, instead of values, revise the formula, to wrap it with the FORMULATEXT function.
If the referenced cell does not contain a formula, the result will be #N/A, as in cell C5, below.
If you want to hide any errors in the formula results, add the IFERROR function. The IFERROR function has 2 arguments:
In this example,
To hide the errors:
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.
To complete the cell checking grid, copy the completed formula to the remaining cells:
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.
To test the grid results, change the structure on one sheet:
To restore the sheet structure:
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 CheckFalues 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.
Last updated: February 14, 2018 1:23 PM