I've been looking for a good excuse to show how to build custom Excel functions, and since there's no built-in Excel information function that returns the current sheet name into a designated cell, I guess this is my chance. You can store your new function in a personal macro workbook so that it's available for any workbook, and you can include it in the Function Wizard with the other information functions. Just follow the steps outlined below.
1. If you have a personal macro workbook, unhide it so that you can add your new function to it: Select Window--Unhide, select the file personal, and click OK.
If you don't have a personal macro workbook (the Unhide option will be greyed out), start in a blank worksheet; you'll create a personal macro workbook with the following steps.
2. Create your custom function by selecting Insert--Macro--Module, and entering the macro shown in the figure.
3. Add your new custom function to Excel's list of regular functions: Select Tools--Macro, select SheetName, and click Options. In the Macro Options dialogue box, enter the following in the Description field: Returns name of the worksheet where reference is located. Otherwise, returns active sheet. In the Function Category field, select Information from the drop-down list. The Function Category determines where your macro will show up in the Function Wizard, and the Description is the text that will appear when you select the function in the wizard's list. In the Status Bar Text field, enter Name of active sheet, then click OK and Close.
4. Now, hide and save your personal macro workbook: Select Window--Hide to hide the workbook, then exit from Excel, whereupon Excel will prompt you to save the hidden workbook. If you started with your personal.xls, just confirm the save. If you started with a blank sheet, navigate to your Excel\Xlstart directory and save the file as personal. Because files in this directory are opened automatically whenever you start Excel, your function will be available for use in all future workbooks.
5. When you're ready to use your new function, select Insert--Function. In step 1 of the Function Wizard, select Information in the Function Category list, select personal.xls!SheetName in the Function Name list, and click Next. In step 2, enter a cell address in the "reference" field and click Finish. Excel returns the name of the current sheet in the active cell. To get the name of a sheet other than the current one, in step 2 enter sheetx!a1, where sheetx is the name of the other sheet.
To bypass the Function Wizard, just enter the formula =personal.xls!sheetname(A1) in a cell in the worksheet where you want the sheet name to appear. Excel will return the name of the current sheet. To return the name of another sheet in the current workbook, enter the formula =personal.xls!sheetname(sheetx!A1), where sheetx is the name of the other sheet.
- Richard Scoville