'Excel Macros for Beginners' Online Video Course
The Excel Addict - Help with Excel 2013, 2010, 2007, 2003

June 23, 2016
 
Greetings from The Excel Addict
Hi fellow Excel Addict,

Newfoundland iceberg - BrianCareyPhotography.comYou 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.

One last reminder that registration for the 'Excel Macros for Beginners' online video course ends tonight (June 23, 8PM Pacific Time). If you've ever wanted to learn macros, you should check this out now before it's too late.

Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


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.


Francis Hayes (The Excel Addict)



If you missed my last newsletter, you can click here to view it online.


 
TheExcelAddict.com Quote of the Day

"You will never reach your destination
if you stop and throw stones at every dog that barks."

-- Winston Churchill
--

If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

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).

Path File Sheet Names In Header Footer in Microsoft Excel 2007 2010 2013 2016 365

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.

Formula:
=CELL("filename",A1)

My Result:
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 [ ).

Formula:
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
=LEFT("C:\Users\fhayes\Documents\Newsletter\Practice Files\[Get Cell Info.xlsx]Sheet2",72-1)
=LEFT("C:\Users\fhayes\Documents\Newsletter\Practice Files\[Get Cell Info.xlsx]Sheet2",71)
C:\Users\fhayes\Documents\Newsletter\Practice Files\


My example result:
C:\Users\fhayes\Documents\Newsletter\Practice Files\

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).

Formula:
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,
FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)
=MID("C:\Users\fhayes\Documents\Newsletter\Practice Files\[Get Cell Info.xlsx]Sheet2",72+1,91-72-1)
=MID("C:\Users\fhayes\Documents\Newsletter\Practice Files\[Get Cell Info.xlsx]Sheet2",73,18)
Get Cell Info.xlsx


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).

Formula:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))- FIND("]",CELL("filename",A1),1))


=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))- FIND("]",CELL("filename",A1),1))
=RIGHT("C:\Users\fhayes\Documents\Newsletter\Practice Files\[Get Cell Info.xlsx]Sheet2",97-91)
=RIGHT("C:\Users\fhayes\Documents\Newsletter\Practice Files\[Get Cell Info.xlsx]Sheet2",6)
Sheet2

Result: Sheet2


** The CELL function syntax has the following arguments:

Info_type:    Required. A text value that specifies what type of cell information you want to return. The following list shows the possible values of the Info_type argument and the corresponding results.

Reference:    Optional. The cell that you want information about. If omitted, the information specified in the Info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper left cell of the range.

Info_type Returns
"address" Reference of the first cell in reference, as text.
"col" Column number of the cell in reference.
"color" * The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
"contents" Value of the upper-left cell in reference; not a formula.
"filename" * Filename (including full path) of the file that contains reference, as text. Returns empty text ("") if the worksheet that contains reference has not yet been saved.
"format" * Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns "-" at the end of the text value if the cell is formatted in color for negative values. Returns "()" at the end of the text value if the cell is formatted with parentheses for positive or all values.
"parentheses" * The value 1 if the cell is formatted with parentheses for positive or all values; otherwise returns 0.
"prefix" * Text value corresponding to the "label prefix" of the cell. Returns single quotation mark (') if the cell contains left-aligned text, double quotation mark (") if the cell contains right-aligned text, caret (^) if the cell contains centered text, backslash (\) if the cell contains fill-aligned text, and empty text ("") if the cell contains anything else.
"protect" * The value 0 if the cell is not locked; otherwise returns 1 if the cell is locked.
"row" Row number of the cell in reference.
"type" Text value corresponding to the type of data in the cell. Returns "b" for blank if the cell is empty, "l" for label if the cell contains a text constant, and "v" for value if the cell contains anything else.
"width" Column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.
* These values are not supported in Excel Online, Excel Mobile, and Excel Starter.



'Excel Macros for Beginners' Online Video Course


Thanks for supporting this newsletter and website

If you would like to share this newsletter with others...
1) Forward this newsletter by email, but first delete the unsubscribe link at the very bottom so you don't get accidentally unsubscribed
2) Ask your friend/colleague to visit TheExcelAddict.com or send a blank email to theexceladdict(AT)aweber.com
3)  Post a link to TheExcelAddict.com in a company newsletter or website.

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.
"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