I have searched the excel function documentation and general MSDN search but have been unable to find a way to return the sheet name without VBA. Is there a way to get the sheet name in an excel f...

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. A reference to another sheet always includes the sheet name (Sheet1), an exclamation (!), and the cell reference (I6). In this case we want the sheet name to be a reference from another cell in the worksheet and that whole reference to then be calculated.

This blog post looks at using an Excel formula to display the sheet name in a cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet name. For the formula we will be using the CELL, MID and FIND functions. Letâ€™s begin by looking at the CELL function. The INDIRECT function works by evaluating text as a worksheet reference. Here, the name of each sheet is joined to the cell reference (A1) using concatenation: Once concatenation is done, the result is: The INDIRECT function will recognize the value in Cell A1 of Sheet1 and return the value.

When you create an Excel table, Excel assigns a name to the table, and to each column header in the table.When you add formulas to an Excel table, those names can appear automatically as you enter the formula and select the cell references in the table instead of manually entering them. To build a dynamic worksheet reference - a reference to another workbook that is created with a formula based on variables that may change - you can use a formula based on the INDIRECT function. In the example shown, the formula in E6 is: = INDIRECT...