If Excel sheets are set up identically, you can create 3-D formulas, to sum a specific range, in a group of sheets. (You can use other functions in a 3-D formula too, such as Average, Count, Min or Max.)
For example, this workbook has sheets for the East, Central and West regions, and all three sheets are set up the same.
On a summary sheet, I can use a 3-D formula to add up all the region totals, because each total is in the same cell on its sheet – B8
=SUM(East:West!B8)
A Disturbance in the Force
This formula works nicely, until some someone changes the structure on one of the sheets. Maybe a co-worker accidentally inserts a blank row on the Central region sheet. Things like that can easily happen!
Then, things go horribly wrong in the 3-D formula. In the screen shot below, the total in the summary sheet has dropped by almost 20, even though none of the numbers in the workbook have changed.
Compare Values on Different Sheets
If you have a workbook in which the calculations depend on sheets being set up identically, you can set up a comparison grid for a few key cells, to confirm that the structures have not changed. We’ll check the product name cells on each region sheet, by using the INDIRECT function. It creates a reference to a range, based on a text string.
This suggestion came from Rudy from Minneapolis, who sent me a sample file to show how it works. Thanks Rudy!
To get the full details, and a sample file, you can go to the Compare Cells page on my website. I’ll just give a quick overview here.
Create a Grid
To create a comparison grid that checks the product name cells on each region sheet:
- Type sheet names in column B and type cell addresses in row 4.
- In cell C5, type the following formula, which will create a reference to cell A4 on the East sheet:
- =INDIRECT(“‘” & $B5 & “‘!” & C$4)
- Press Enter, and the value from the referenced cell appears in cell C5
Then, copy the formula across and down, to fill the grid. If the cell values are the same on each sheet, you can be confident that the structures have not changed.
But, if there are differences, you can check the sheets, and fix them. In the screen shot below, there was a blank row inserted in the Central sheet, so that row needs to be deleted.
Compare Formulas on Different Sheets
If you’re using Excel 2013 or later, you can set up a similar grid, to compare formulas on different sheet. This grid uses the FORMULATEXT function, which was added in Excel 2013. It shows the formula in a referenced cell. We’ll check the total quantity cell (B8) on each region sheet, and the cell above that (B7).
To create the comparison grid:
- Copy the Values comparison grid, and change the cell addresses in row 4
- Delete any columns that you don’t need (I deleted 2 columns)
- In cell C5, modify the formula, to include the FORMULATEXT function:
- =FORMULATEXT( INDIRECT(“‘” & $B5 & “‘!” & C$4) )
- Press Enter, and the formula from the referenced cell appears in cell C5
You’ll see an #N/A error if the referenced cell does not contain a formula.
Hide the Errors
To hide errors, if the referenced cell doesn’t contain a formula, you can add the IFERROR function. I’ve set it to show two hyphens in the cell, if there is an error, and you could use another value, such as an empty string – “”.
- In cell C5, modify the formula, to include the IFERROR function:
- =IFERROR( FORMULATEXT(INDIRECT(“‘” & $B5 & “‘!” & C$4)) ,”—“)
- Press Enter, and the formula from the referenced cell appears in cell C5, or two hyphens appear, if the cell doesn’t have a formula.
Copy the completed formula to the remaining cells. If the structures are identical, the formulas on all sheets should be the same. If there is a difference, go to the problem sheet and fix it.
Use in Moderation
This technique is a handy way to compare values and formulas on multiple sheets, but try to keep it to a small grid. If you set it up to compare hundreds of cells and sheets, your workbook calculation could grind to a halt!
Both the INDIRECT and FORMULATEXT functions are volatile, so they’ll put a heavy load on your file, if you use them in too many places. Read more about volatile Excel functions on Charles Williams’ website. I wasn’t sure about the FORMULATEXT function, but tested it the Volatile Functions file that you can download from that page.
Get the Details and Download
To get the full details, and a sample file, you can go to the Compare Cells page on my website. The file is in xlsx format, and does not contain macros.
Remember, the FORMULATEXT function only works in Excel 2013 or later.
_________________
