Sometime it's useful to use the file name, file path, or sheet name. Particularly if you're using formulas like INDIRECT() or HYPERLINK(). The problem occurs if the path changes of the sheet name is changed, you need some way for those to be dynamically updated.
So this is how I've used CELL() and HYPERLINK().
CELL()
- File path, file name, Sheet Name (FullPath)
- =CELL("filename") (You can use it to point to another sheet as well, =CELL("filename",AnotherSheet!A1))
- File path
- =LEFT(FullPath,FIND("[",FullPath,1)-1)
- File name
- =MID(FullPath,FIND("[",FullPath,1)+1,FIND("]",FullPath,1)-FIND("[",FullPath,1)-1)
- Sheet name (Current)
- =RIGHT(FullPath,LEN(FullPath)-FIND("]",FullPath,1))
HYPERLINK()
Using the information you get using CELL, you can use it to create a hyperlink using HYPERLINK(). See the sample file for more details.
CAVEATS
- CELL() (without reference to a particular cell) updates to the actively working-in workbook.
- CELL() cannot update file paths for closed workbooks. That is, if you have the linked workbook closed, then move it, the workbook with the CELL() formula won't updated (you'll get an link error).
The value returned by CELL is dependent on the active workbook. That is, if you have two workbooks open with the first one (e.g. A.xlsx) having the CELL formula, when you select and start typing in the second workbook (e.g. B.xlsx) the result for the CELL formula in A.xlsx show the file path (or whatever information you've gotten it to show) for B.xlsx. So using cell doesn't work well if you have multiple workbooks open.
Similarly, if you switch to another worksheet, and start using it, CELL will update with the information for that sheet. So it's best to specifically point it to a cell on the sheet (with the sheet reference) you're interested in.
Once more, Use CELL() with a cell reference!