AuditBible
January 23, 2018, 04:08:53 AM *
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
Sirius Satellite Radio Inc.
 
   Home   News Alerts Help Search Calendar Members Contributions Staff List Login Register  

Microsoft Store
Del.icio.us Digg FURL FaceBook Stumble Upon Reddit SlashDot Google Bookmarks

Pages: 1   Go Down
  Add bookmark  |  Print  
Author Topic: Insert the Tab name into your worksheet  (Read 2770 times)
 
0 Members and 1 Guest are viewing this topic.
beancounter
Administrator
*****
Offline Offline

Gender: Male
Experience: 1985
Posts: 168



Activity
100%


View Profile WWW
« on: September 04, 2008, 09:41:47 AM »

If you need the name of your sheet (the text that appears in the tab) to appear on the sheet itself, there are two ways to accomplish the task:

You can simply select Header and Footer from the View menu, then click on the Custom Header (or Footer) button and click the Tab button which will insert the $[Tab] code.  This places the name from the Tab into the Header or Footer.

But, what if you want the Tab name to appear in a cell somewhere on the sheet?  In that case, you need this formula:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
If you simply entered =CELL("filename") into a cell, the formula would return the full path name (for example, if your workbook is saved to the desktop, and your tab name was "January-09", the path would be C:\Desktop[My Workbook.xls]January-09).

This formula will return the tab, or sheet, name from the file name by isolating everything to the right of the "]" and displaying the result in the cell.

The only caveat, however, is that the formula will return "#REF!" if you enter it in cell A1 (or if you delete Column A or Row 1).  It will also display "#REF!" if you have not yet saved the workbook: don't worry -- just save it, then close and reopen it to see the correct result.
Logged

bc
AuditorLisa
Global Moderator
*****
Offline Offline

Gender: Female
Occupation: Supervisor - Field Audit Services
Location: California
Experience: 1989
Posts: 35



Activity
0%

Home of the Free because of the Brave


View Profile
« Reply #1 on: September 04, 2008, 01:33:16 PM »

This is great!   I like it!
Logged
beancounter
Administrator
*****
Offline Offline

Gender: Male
Experience: 1985
Posts: 168



Activity
100%


View Profile WWW
« Reply #2 on: September 05, 2008, 07:44:50 PM »

Thanks for your feedback - I'm glad you like it Wink

I'm not sure how much use people will get out of some of these Excel tips, but I figured it's better to post them (no matter how obscure) and maybe they'll be just the thing someone needs for some project they happen to be working on.
Logged

bc
Pages: 1   Go Up
  Add bookmark  |  Print  
 
Jump to:  

Microsoft Store
Unique Hits: 1851185
Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2015, Simple Machines
AuditBible © 2008 | Sitemap
Valid XHTML 1.0! Valid CSS!