CODEWITHSUNDEEP
excelpowerquery
Xodarap
Xodarap

Reputation: 381

Extract the name of the workbook where the formula is saved and not the name of the workbook currently in use

I want to find an excel or Power query M (not vba) formula to extract the filename of the workbook where this formula is executed. Internet seems to know only one :

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

unfortunately this formula provides only the name of the workbook currently in use, so, if I use a second workbook before executing my function in my first workbook, the name given by this formula will be the name of my second file, which causes terrible mistakes.

Is there a more practical way of doing this ?

Upvotes: 0

Views: 55

Answers (1)

BigBen
BigBen

Reputation: 50162

If you specify the second (reference) parameter of CELL, then the formula will be "locked" to the sheet/file in question:

=MID(CELL("filename",A1),SEARCH("[",CELL("filename",A1))+1, SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-1)

This is discussed in the CELL docs as well as by Chip Pearson here.

Upvotes: 2

Related Questions