Workbook in Excel

Excel Tutorials for beginners, Intermediates and experts.

List worksheet index numbers in Excel

To list the index numbers of sheets in an Excel workbook, you can enter the sheet names, then use a formula based on the SHEET and INDIRECT functions. The SHEET function then returns the current index for each sheet as listed. Formula =SHEET(INDIRECT(name&”!A1″)) Explanation In the example shown, the formula in C5 is: =SHEET(INDIRECT(B5&”!A1″)) How… read more »

List sheet names with formula in Excel

To list worksheets in an Excel workbook, you can use a 2-step approach: (1) define a named range called “sheetnames” with an old macro command and (2) use an INDEX formula to retrieve sheet names using the named range. Formula =GET.WORKBOOK(1)&T(NOW()) Note: because this formula relies on a macro command, you’ll need to save as a macro-enabled workbook… read more »

How to reference named range different sheet in Excel

This tutorials shows how to reference a named range on another sheet. To achieve this, you can use the INDIRECT function with the required sheet syntax. Formula INDIRECT(“‘”&sheet&”‘!”&name) Explanation In the example shown, the formula in D6 is: =SUM(INDIRECT(“‘”&B6&”‘!”&C6)) Which returns the sum of the named range “data” on Sheet1. How this formula works The… read more »

How to get Excel workbook path only

If you want to get the workbook path (directory) only, you can do so with a formula that uses the LEFT and the FIND function. The result will look something like this: Drive:\path\to\file\ //Windows Drive:path:to:file: //Mac Formula =LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))-1) Explanation How the formula works The cell function is used to get the full file name and… read more »

How to check worksheet name exists in Excel

To test if a worksheet name exists in a workbook, you can use a formula based on the ISREF and INDIRECT functions. Formula =ISREF(INDIRECT(“sheetname”&”!A1″)) Explanation In the example shown, the formula in C5 is: =ISREF(INDIRECT(B5&”!A1″)) How this formula works The ISREF function returns TRUE for a valid worksheet reference and FALSE is not. In this… read more »

Get workbook name and path without sheet in Excel

If you want to get the current workbook’s full name and path without a sheet name, you can use a formula that employs several text functions to strip off the sheet name. The final result will be a text string that looks like this: path[workbook.xlsm] Formula =SUBSTITUTE( LEFT(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))-1),”[“,””) Explanation How the formula works The CELL… read more »

How to get sheet name only in Excel

If you want to get the sheet name only (i.e. the sheet name without the file name or path) you can do so with rather long formula that uses the MID function along with the FIND function. The final result will look something like this: Sheet1 Formula =MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,255) Explanation How the formula works The cell… read more »

How to create dynamic worksheet reference in Excel

To create a formula with a dynamic sheet name you can use the INDIRECT function. Note: The point of this approach is it lets you to build a formula where the sheet name is a dynamic variable. So, for example, you could change a sheet name (perhaps with a drop down menu) and pull in information… read more »

Create dynamic workbook reference to another workbook in Excel

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. See example below: Formula =INDIRECT(“‘[“&workbook&”]”&sheet&”‘!”&ref) Explanation In the example shown, the formula in E6 is: =INDIRECT(“‘[“&B6&”]”&C6&”‘!”&D6) How this formula works… read more »

How to retrieve workbook name only in Excel

If you want to get the workbook name only (i.e. the file name without path or sheet name) you can do so with a rather long formula that uses the MID function along with the FIND function. Formula =MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))+1,FIND(“]”, CELL(“filename”,A1))-FIND(“[“,CELL(“filename”,A1))-1) Explanation How the formula works The cell function is used to get the full file… read more »

Sidebar