 |
|
| TheExcelAddict.com | |
| March 3, 2020 |
|
| Hi fellow Excel Addict, | |
 Despite
moving closer to spring, there's still lots of snow on the
ground here in Newfoundland. I finally got a chance to go
snowshoeing a few days ago for the first time this winter
due to a nagging knee problem I've been dealing with.The longer days along with the bright sunshine makes the the latter part of winter much more enjoyable than the dark, dreary and stormy days of December. Thanks for taking some time out of your busy schedule again this week to check out my newsletter. If you like it, please share it on social media and with your colleagues or any other Excel users you know. Have a great day and keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
| |
|
Having a positive attitude can help us in difficult times. I hope today's quote will help you foster a positive attitude today. Quote of the Day "If your happiness depends on what somebody else does, I guess you do have a problem." -- Richard Bach -- |
|
|
If you have a favourite quote, send it to me
and I may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN SECONDS' TIP |
|
Create A Hyperlink Formula To The Current Workbook's Folder |
|
Do you find that sometimes you need to open the folder for the workbook you are currently working on? Normally you would do that by going to File Explorer and then navigating to the folder. If you're like me, however, and frequently use hyperlinks or shortcuts to open many of your workbooks, it's sometimes hard to remember which folder that workbook is located in. For those workbooks where you frequently need to access the folder of the current workbook (e.g. for related files), I'm going to give you a hyperlink formula that you can put in any workbook that will take you directly to it. There are two parts to the formula. PART 1: Use the MID, CELL and FIND Functions to Return the Current Workbook's Location =MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)
 For more details on how to extract the full path, file or sheet name click here. PART 2: Use the HYPERLINK Function to Create a Live Link to That Location =HYPERLINK(MID(CELL("filename"),1,FIND("[",CELL("filename"))-1),"Go
to Folder")
 The HYPERLINK function has two parts: HYPERLINK(link_location,
[friendly_name])
The link_location, for this example, is where we insert the above formula that returns the workbook's path. The friendly_name is optional (denoted by the square brackets) and where we can type any text we want to display in the cell rather than the actual link text. In the above example we can type some other text instead of "Go to Folder". If you leave it out the friendly_name part, the link's text will be displayed in the cell.  The nice thing about this formula is that you can copy it exactly as it is to any workbook and it will work. Try it now. Copy this formula and paste it into any of your workbooks. =HYPERLINK(MID(CELL("filename"),1,FIND("[",CELL("filename"))-1),"Go
to Folder")
Note that new workbooks must be saved before the CELL("filename") part of this formula will work. For more details on how to extract the full path, file or sheet name click here. |
|
|
|
|
|
|
|
 |
|
|
|
|
 |
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |