=CELL("filename",$A$1)
37. Get Workbook Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once.
=REPLACE(LEFT(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))-
1),1,FIND("[",CELL("filename",$A$1)),"")
38. Get Sheet Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once.
Use following formula -
=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")
Make sure that A1 is used in the formula. If it is not used, it will extract sheet name for the
last active sheet which may not be one which we want.
If you want the sheet name for last active sheet only, then formula would become
=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")
39. Get Workbook's Directory from Formula
Before getting this, make sure that you file has been saved at least once as this formula is
dependent upon the file path name which can be pulled out by CELL function only if file has
been saved at least once.
If your workbook is located in say C:\Excel\MyDocs, the formula to retrieve the directory
for this would be
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)
40. Last Day of the Month for a Given Date
Suppose, you are given a date say 10/22/14 (MM/DD/YY) and we want to have the last
date of the month for the given date. Hence, you needs an answer of 10/31/14. The formulas
to be used in this case -
=EOMONTH(A1,0)
=DATE(YEAR(A1),MONTH(A1)+1,0)
