Excel OfficeExcel comes with multiple Lookup and Reference functions that let you find matching values. The Lookup and Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets
To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions. See example below; Note: The ADDRESS function builds an address based on a row and column number. Formula =ADDRESS(ROW(range),COLUMN(range)) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data))… 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 »
This tutorial shows how to Highlight unprotected cells in Excel using the example below; Formula =CELL(“PROTECT”,A1)=0 Explanation To highlight unprotected cells (cells that are unlocked) with conditional formatting, you can use a formula based on the CELL function. In the example shown, the custom formula used to set up conditional formatting is: =CELL(“PROTECT”,A1)=0 Where A1 represents the… read more »
This tutorial shows how to Get address of lookup result in Excel using the example below; Formula =CELL(“address”,INDEX(range,row,col)) Explanation To get the address of a lookup result derived with INDEX, you can use the CELL function. In the example shown, the formula in cell G8 is: =CELL(“address”,INDEX(B5:D11,MATCH(G6,B5:B11,0),2)) Which returns an address of $C$8, the address… read more »