How to Use Excel Cell Data to Reference a Sheet Name

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.
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.
3. Click on the tab for Sheet2 and enter 'Orange' in cell A1.
4. Click on the tab for Sheet3 and enter 'Screwdriver' in cell A1.
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.
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.