exceltoxl.com

Hi there,

Have you come across this situation in your work. This is really amazing trick to extract the active sheet name to a cell. The formula goes like this.

=mid(cell("filename",a1),find("]",cell("filename",a1))+1,111)

dynamic formula, what if cell A1 deletes either by Row or Column:

=mid(cell("filename"),find("]",cell("filename")+1,111)

EXPLANATION:

...

CELL("filename",a1) gives the complete path of the file name either it is on local drive or from external source.

FIND("]",CELL("filename",a1))+1 gives the number of characters present to reach the character "]"

111 is the character selection from the filename path.

Mix together gives the SheetName of the active sheet.

This formula is very useful because it gets updated when ever the sheet name gets changed by the user.

Note: Make sure the above formula works on the saved worksheet. Otherwise, you will get a #VALUE! error.

That's it. Magic works!!!

Here is an another approach:  

=TRIM(RIGHT(SUBSTITUTE(CELL("filename"),"]",REPT(" ",31)),31))

DO YOU USE FILENAME?

You can share your experience and findings about a function "filename" using the below comments section. If you like this post please do share it on your social networking sites by clicking on the icons placed around the blog.

If you want to support me, REGISTER here for free. Yes, Its absolutely free.

SOME IMPORTANT LINKS:

List FileNames in a Folder

Count Worksheets in a Workbook

Conditional Formatting on your finger tips

Information Functions Week

Thanks,