␡
- A Hard Cell
- Using the CELL() Function
- Use CELL() to Identify a Range
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Use CELL() to Identify a Range
For my final CELL() example, consider a complex workbook whose main worksheet contains a bunch of formulas that summarize the raw data located in the named range MyData. As the workbook evolves, the location of that named range might change, and a user who wants to review the raw data may have trouble finding it. You can use CELL() to display the address of the range—or more precisely, of the cell at the top left of the range. The format is like this:
[MyWorkbook.xls]SheetName!$A$1
Because you don’t need the workbook name displayed, use a formula like this:
="Raw data located at " " RIGHT(CELL("address",MyData),LEN(CELL("address",MyData))-FIND("]",CELL("address",MyData)))
Assuming that the range is on the worksheet named Data with its top left corner in cell B2, the resulting display will be the following:
Raw data located at Data!$B$2