June 23, 2016
You may know that I live on the island of Newfoundland on Canada's east coast. Being an island in the North Atlantic, our weather is even more volatile than most places. When the wind is from the north, it's cold here winter or summer. When it's from the east (off the ocean) it's cold, damp and foggy and, in the spring, icebergs come close to shore. When it's from the southwest (thankfully, our prevailing wind), the weather is much nicer. In the summer, southwest winds usually bring the warm air from the central Canada or northeastern United States.
So, over the past several days, we've gotten into a nice southwesterly flow with nice warm temps. Unfortunately, that has pushed the icebergs further out to sea. I was hoping to take some iceberg pictures to post in my newsletter but since I don't live right on the coast, I don't always know when they are around. And there won't be any icebergs coming close to shore until we get a few days of easterly wind. As much as I love to see icebergs, I'm not wishing for those (cold) easterly winds.
The pic above is by a local photographer Brian Carey.
If you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you 'One Keyboard Shortcut That Quickly Turns On, Off and Resets Filters'. You can read the tip here.
Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
P.S. I apologize if some of the tables in my tip below are misaligned. I noticed that when I copied my newsletter from my HTML editor to my my Aweber editor, some of the tables didn't get translated properly and appear misaligned in some browsers.
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Formulas that Put Sheet, Workbook, or Folder Name in a Cell
If you want to print the name of your worksheet, workbook and/or its folder you can do that using the Header & Footer Tools (Insert, Header & Footer) or through the Page Setup settings (Page Layout, Print Titles, Header/Footer).
However, there may be times when you would like to include these names in the worksheet itself.
The key to doing this is using the little-known CELL worksheet function. The CELL worksheet function is used to return information about the formatting, location or contents of a cell.
Syntax: =CELL(info_type, [reference])
When we use "filename" for the info_type argument** in the CELL worksheet function, it returns the entire path, workbook name, and sheet name for a given cell reference. Note that will not work with unsaved workbooks.
To see how this works, let's take a look at this example.
Assume I am entering these formulas in Sheet2 of my workbook named 'Get Cell Info.xlsx' which is saved in my C:\Users\fhayes\Documents\Newsletter\Practice Files\ folder...
To practice this for yourself, try these formulas in any saved workbook
For the full path, workbook name and sheet name
In any cell in the worksheet, enter the following formula. You can use a reference to any cell on the worksheet. It doesn't matter which one. I keep it simple and always use A1.
C:\Users\fhayes\Documents\Newsletter\Practice Files\[Get Cell Info.xlsx]Sheet2
For just the path of the workbook
Use the same base formula to return the entire path, filename and sheet name and, with the help of the LEFT and FIND functions, extract just the path (i.e. everything to the left of the [ ).
My example result:
For just the workbook name
Use the same base formula and then, with the help of the MID and FIND functions, extract just the filename (i.e everything between the [ and ] characters).
My example result:
Get Cell Info.xlsx
Just the worksheet name
Use the same base formula and, with the help of the RIGHT, LEN and FIND functions, extract just the sheet name (i.e. everything to the right of the ] character).
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630