When using Excel, sometimes it's necessary to pull information from multiple worksheets or pull a listing in a cell to create a worksheet address and reference. This is useful when you're using one master worksheet to control the links to several subordinate workbooks, and it provides an introduction to the INDIRECT() function, which is one of the most useful tools in Excel's library of functions.
Step 1
Create a workbook in Excel. It will start with three worksheets, shown by tabs along the bottom of the screen. By default, these are named "Sheet1," "Sheet2" and "Sheet3." Sheet1 will be active.
Video of the Day
Step 2
Enter "'Sheet2" in cell A2. Enter "'Sheet3" in cell A3. Note that both of these prepend the word "Sheet" with a single quote ('), to indicate that it's a text value.
Step 3
Click on the tab for Sheet2 and enter "Orange" in cell A1.
Step 4
Click on the tab for Sheet3 and enter "Screwdriver" in cell A1.
Step 5
Click on the tab for Sheet1, and in cell B2, enter the following formula: "=INDIRECT("'"&A2&"'!A1")" (without the quotation marks before the equals sign or after the final parentheses). Once you hit "Enter," the formula will evaluate, and the cell will show the word "Orange." The INDIRECT() function is substituting the contents of cell A2 into the normal cross-sheet cell reference format. This gives the same result as if you'd entered " =Sheet2!A1" into cell C2.
Step 6
Copy cell B2 and paste it into cell B3. Cell B3 will now show the word "Screwdriver." This is because the relative reference in the formula you entered is now pulling the name of the worksheet from cell A3 rather than A2.
Video of the Day