Then, copy the formula across and down, to fill the grid. Press Enter, and the value from the referenced cell appears in cell C5.In cell C5, type the following formula, which will create a reference to cell A4 on the East sheet:.Type sheet names in column B and type cell addresses in row 4.To create a comparison grid that checks the product name cells on each region sheet:
To get the full details, and a sample file, you can go to the Compare Cells page on my website. This suggestion came from Rudy from Minneapolis, who sent me a sample file to show how it works. It creates a reference to a range, based on a text string. We’ll check the product name cells on each region sheet, by using the INDIRECT function. 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. 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. Then, things go horribly wrong in the 3-D formula. Maybe a co-worker accidentally inserts a blank row on the Central region sheet. This formula works nicely, until some someone changes the structure on one of the sheets. 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 (You can use other functions in a 3-D formula too, such as Average, Count, Min or Max.)įor example, this workbook has sheets for the East, Central and West regions, and all three sheets are set up the same. If Excel sheets are set up identically, you can create 3-D formulas, to sum a specific range, in a group of sheets.