- 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
Using the CELL() Function
When you are working on a workbook in Excel, there’s no problem finding out the filename and where it is located (its path). But suppose a workbook is being printed—it can be a good idea to have the path and filename included on the printout—and to have the information automatically updated if the filename or location is changed. =CELL("filename") does this, but it returns the information with the filename in brackets and the sheet name too, like this:
c:\data\sales\[EastCoast.xls]Sheet1
You want it displayed as follows:
c:\data\sales\EastCoast.xls
You can do it with the following formula that uses the LEFT(), MID(), and FIND() functions to extract just the path and filename from the text returned by CELL():
=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1) " MID(CELL("filename"),FIND("[",CELL("filename"))+1,(FIND("]",CELL("filename"))-FIND("[",CELL("filename")))-1)
Another way I have used CELL() is to display missing year information if needed. Cells that contain date values can be formatted in a way that displays the day and month, but not the year. In this case, you might want to display the year in a heading. If the cells are formatted to display the year, however, the heading does not need to contain the year (see Figure 1). On the left, where the dates are formatted to exclude the year, the heading displays the year. On the right, where the dates are formatted to show the year, the heading does not include the year.
)
Figure 1 Using the CELL() function to control heading display.
This is accomplished by placing the following formula in cell B2:
=IF(CELL("format",B5)="D2","New Members for " " YEAR(B5), "New Members")
This formula is somewhat oversimplified because some of the "no year" date formats cause CELL() to return values other than D2. You can find the other relevant return values in Excel help and modify the formula as needed.