Tom’s Tutorials For Excel: Viewing All Worksheets With One INDIRECT Formula
Here’s how you can use Data Validation with the
INDIRECT function in a single formula to quickly and easily view the contents of other worksheets in your workbook.
Suppose your workbook is the company budget, designed so the company’s regions occupy their own budget worksheets. For example, the following picture shows worksheets for North, South, East, and West regions. A Summary worksheet is also designed the same, and is the focus of this example.
On the Summary worksheet in cell H2, Data Validation is applied, with the list of allowable entries limited to the regions’ worksheet tab names. I selected cell H2 and pressed
Alt+D+L to show the Data Validation dialog box. In the Source field for List, I typed in those tab names separated by a comma, as shown in the following picture.
Still on the Summary worksheet, I selected the same range of cells that contain budget numbers for the other regions. It’s a valuable benefit from designing the workbook with all worksheets laid out the same. In the active cell (B4 in this example) I typed the formula…
…and I pressed
Ctrl+Enter which applied that formula to all selected cells.
Now while never leaving the Summary worksheet, it’s a simple matter of selecting the worksheet name from the drop down list in cell H2 depending on which region I want to quickly see. In the following picture, I selected the East Region.
Here are the East Region’s numbers.