Excel OfficeExcel Information Functions return info about the current environment, including platform,
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 »
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 »
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 »
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 »
If you want to get the current workbook’s full path, name, and sheet with a formula, you can use the CELL function and a reference to any cell in the workbook. CELL will return the name in this format: path[workbook.xlsx]sheetname Note that you must save the worksheet in order to get the a result. Formula… read more »
To check if a cell contains specific text, you can use the SEARCH function together with the ISNUMBER function. In the generic version, substring is the specific text you are looking for, and text represents text in the cell you are testing. Formula =ISNUMBER(SEARCH(substring,text)) Explanation In the example shown, the formula in D5 is: =ISNUMBER(SEARCH(C5,B5)) This formula returns TRUE if… read more »
To test a cell for one of many strings, while excluding others, you can use a formula based on the SEARCH, ISNUMBER, and SUMPRODUCT functions. Formula =(SUMPRODUCT(–ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(–ISNUMBER(SEARCH(exclude,A1)))=0) Note: this formula returns either 1 or zero, which are handled like TRUE and FALSE in formulas, conditional formatting, or data validation. Explanation In the example shown the… read more »
IF function and ISERROR function are used to check for an error in Excel. This example shows you how to create an array formula that counts the number of errors in a range. 1. We use the IF function and the ISERROR function to check for an error in the record below: Explanation: the IF function returns 1, if an… read more »