Any way to get worksheet name from function?

Is there any way for me to access the name of the current Worksheet tab, to 
put it into a cell? I need a cell whose name changes based on the name 
entered on the Worksheet tab.

Thanks,
Colin 

0
colin6444 (11)
1/25/2009 7:53:52 PM
excel 39880 articles. 2 followers. Follow

7 Replies
336 Views

Similar Articles

[PageSpeed] 46

Copied from Bob Phillips' site........note: leave the "filename" as is.  Do
not substitute your file name.

File path, file and worksheet name:
=CELL("filename",A1) 

File path only:
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1) 

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

The sheet name:
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) 

Restrictions
This technique only works for workbooks that have been saved, at least once.

http://www.xldynamic.com/source/xld.xlFAQ0002.html

Bob's site is temporarily down.


Gord Dibben  MS Excel MVP

On Sun, 25 Jan 2009 14:53:52 -0500, "Colin" <colin@nospam123meritgames.com>
wrote:

>Is there any way for me to access the name of the current Worksheet tab, to 
>put it into a cell? I need a cell whose name changes based on the name 
>entered on the Worksheet tab.
>
>Thanks,
>Colin 

0
Gord
1/25/2009 8:07:00 PM
That's perfect. Thanks!

I had looked at the cell() function, but had not tried the "filename" 
option.

Related, but I don't think this is possible - along the same lines, is there 
any way to get the name of the next sheet?

Thanks again,
Colin


"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:behpn4l4cavqpu5rl8q8k2ma32usfvbt4c@4ax.com...
> Copied from Bob Phillips' site........note: leave the "filename" as is. 
> Do
> not substitute your file name.
>
> File path, file and worksheet name:
> =CELL("filename",A1)
>
> File path only:
> =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
>
> File name only:
> =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)
>
> The sheet name:
> =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
>
> Restrictions
> This technique only works for workbooks that have been saved, at least 
> once.
>
> http://www.xldynamic.com/source/xld.xlFAQ0002.html
>
> Bob's site is temporarily down.
>
>
> Gord Dibben  MS Excel MVP
>
> On Sun, 25 Jan 2009 14:53:52 -0500, "Colin" 
> <colin@nospam123meritgames.com>
> wrote:
>
>>Is there any way for me to access the name of the current Worksheet tab, 
>>to
>>put it into a cell? I need a cell whose name changes based on the name
>>entered on the Worksheet tab.
>>
>>Thanks,
>>Colin
> 

0
colin6444 (11)
1/25/2009 8:48:57 PM
Hi,

You can shorten the previous suggestion to

=MID(CELL("Filename"),FIND("]",CELL("Filename"))+1,31)

31 is chosen because the maximun number of characters for a sheet name is 
31.  The is no need to reference any cell when using this version of the 
formula.

Another point, this formula recalculates when the spreadsheet recalculates, 
which means when you move from another sheet to the sheet with the formula it 
will display the incorrect sheet name until the sheet recalculates.


-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Colin" wrote:

> Is there any way for me to access the name of the current Worksheet tab, to 
> put it into a cell? I need a cell whose name changes based on the name 
> entered on the Worksheet tab.
> 
> Thanks,
> Colin 
> 
> 
0
1/26/2009 12:33:01 AM
If you leave the cell referfence in the formula, a re-calc is not necessary
when switching sheets.


Gord

On Sun, 25 Jan 2009 16:33:01 -0800, Shane Devenshire
<ShaneDevenshire@discussions.microsoft.com> wrote:

>Another point, this formula recalculates when the spreadsheet recalculates, 
>which means when you move from another sheet to the sheet with the formula it 
>will display the incorrect sheet name until the sheet recalculates.

0
Gord
1/26/2009 12:57:12 AM
Thanks!

Any way to get the names of other worksheets? In other words, is there a 
reference to something like CurrentSheet+1 or CurrentSheet-1?

Thanks,
Colin


"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:pg2qn45el5jf2ak601uf67dqrt1vkgjf78@4ax.com...
> If you leave the cell referfence in the formula, a re-calc is not 
> necessary
> when switching sheets.
>
>
> Gord
>
> On Sun, 25 Jan 2009 16:33:01 -0800, Shane Devenshire
> <ShaneDevenshire@discussions.microsoft.com> wrote:
>
>>Another point, this formula recalculates when the spreadsheet 
>>recalculates,
>>which means when you move from another sheet to the sheet with the formula 
>>it
>>will display the incorrect sheet name until the sheet recalculates.
> 

0
colin6444 (11)
1/27/2009 9:41:04 PM
I don't know of any formula method without using a UDF

Function AnySht(Name As String, num As Integer)
Application.Volatile
    N = Application.Caller.Parent.Index
    If N = 1 Then
        AnySht = CVErr(xlErrRef)
    Else
        AnySht = Sheets(N + num).Name
    End If
End Function

=anysht("name",x)

where x is positive or negative number away from current sheet


Gord 


On Tue, 27 Jan 2009 16:41:04 -0500, "Colin" <colin@nospam123meritgames.com>
wrote:

>Thanks!
>
>Any way to get the names of other worksheets? In other words, is there a 
>reference to something like CurrentSheet+1 or CurrentSheet-1?
>
>Thanks,
>Colin
>
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
>news:pg2qn45el5jf2ak601uf67dqrt1vkgjf78@4ax.com...
>> If you leave the cell referfence in the formula, a re-calc is not 
>> necessary
>> when switching sheets.
>>
>>
>> Gord
>>
>> On Sun, 25 Jan 2009 16:33:01 -0800, Shane Devenshire
>> <ShaneDevenshire@discussions.microsoft.com> wrote:
>>
>>>Another point, this formula recalculates when the spreadsheet 
>>>recalculates,
>>>which means when you move from another sheet to the sheet with the formula 
>>>it
>>>will display the incorrect sheet name until the sheet recalculates.
>> 

0
Gord
1/27/2009 11:13:45 PM
Yeah, I ended up doing it in VBA, but I was hoping for something that I 
could just embed in a cell so I could leave out the code for simplifying 
security issues.

Thanks,
Colin


"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:lb4vn4li8mbea16d1rf0nrvdp7paqvl4vd@4ax.com...
>I don't know of any formula method without using a UDF
>
> Function AnySht(Name As String, num As Integer)
> Application.Volatile
>    N = Application.Caller.Parent.Index
>    If N = 1 Then
>        AnySht = CVErr(xlErrRef)
>    Else
>        AnySht = Sheets(N + num).Name
>    End If
> End Function
>
> =anysht("name",x)
>
> where x is positive or negative number away from current sheet
>
>
> Gord
>
>
> On Tue, 27 Jan 2009 16:41:04 -0500, "Colin" 
> <colin@nospam123meritgames.com>
> wrote:
>
>>Thanks!
>>
>>Any way to get the names of other worksheets? In other words, is there a
>>reference to something like CurrentSheet+1 or CurrentSheet-1?
>>
>>Thanks,
>>Colin
>>
>>
>>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>>news:pg2qn45el5jf2ak601uf67dqrt1vkgjf78@4ax.com...
>>> If you leave the cell referfence in the formula, a re-calc is not
>>> necessary
>>> when switching sheets.
>>>
>>>
>>> Gord
>>>
>>> On Sun, 25 Jan 2009 16:33:01 -0800, Shane Devenshire
>>> <ShaneDevenshire@discussions.microsoft.com> wrote:
>>>
>>>>Another point, this formula recalculates when the spreadsheet
>>>>recalculates,
>>>>which means when you move from another sheet to the sheet with the 
>>>>formula
>>>>it
>>>>will display the incorrect sheet name until the sheet recalculates.
>>>
> 

0
colin6444 (11)
4/10/2009 1:17:32 PM
Reply:

Similar Artilces:

rename worksheet tabs
Hello, Maybe you guys can help me. Is there a way rename the worksheet tabs in an Excel file that I've already created? Example... I've already created a fancy Excel report using SSRS. The problem is...SSRS has no way to rename the 10 worksheet tabs to more meaningful names. Any ideas? Thanks! Double-click on the tab. That will let you rename the sheet. Alternatively, right-click on the tab, and choose "Rename" from the menu. -- David Biddulph "fix me" <brickpack@gmail.com> wrote in message news:85c00dd8-0a8f-4c32-8d12-f71c5ebcda...

How do I get started with Outlook?
I am trying to use outlook and I just can't get it to work. I put in an e-mail address and all that but whenever I go to send an e-mail it automatically goes to my outbox. What am I doing wrong? "LauraAshly" <LauraAshly@discussions.microsoft.com> wrote in message news:1F311444-011A-4818-9252-1D35FE74CA08@microsoft.com... >I am trying to use outlook and I just can't get it to work. I put in an > e-mail address and all that but whenever I go to send an e-mail it > automatically goes to my outbox. What am I doing wrong? When you say "I put in an e-mai...

How to get POS out of Evaluation Mode?
Both of my POS softwares are running in evaluation mode. When I log in a message pops up saying I'm operating on a license that is due to expire in September and therefore it is running in evaluation mode. How do I get POS out of evaluation mode and does it have anything to do with my license? Yes, it has something to do with your license. Insert the license dongle in POS register 1. "G E" wrote: > Both of my POS softwares are running in evaluation mode. When I log in a > message pops up saying I'm operating on a license that is due to expire in > Septemb...

Print Queue
If I right-click, and select |New then print it shows document+sequential number of docs opened/printed in the current session of word. Is there a way to pass, update a template that is opened as New so that Word's title bar does not show Document+sequence number ? TIA You could use an autonew macro in the document template with the line ActiveWindow.Caption = "" but frankly I don't recommend it. If nothing else the indication in the title bar is a warning that the document has not been saved. -- <>>< ><<> ><<> <>...

Best Way ?
G'day All, Looking for some information. With Outlook you can have all your contacts...address, email address, yada, yada, yada. Can I use this information in to say mail merge with Word and email a letter to the ppl with email addresses? Or is this better done with a Access? The reason I ask is that we want to deliver an email to all our clients with the latest promotions. And want it to be a bit more automated? Any information or pointers in the right direction would be appreciated. Have a good one, Scott V You can use either Access or Outlook as the data source for a ma...

Need Help with Irregular IRR function
I am doing a big project for work that requires inputing a whole bunc of irregular cash inflows and outflows over a period of about 3 years. I tried ordering all the cash flows chronologically so that it woul look like: Date Cash Flow and the outflows were negative, inflows were positive. I thought I covered all the possible problems, but I was wrong becaus Excel kept telling me that when i selected all the values, (Cash Flo and/or date) I had an error because I was dividing by Zero. I trie taking out all the zero values for cash flow, but still I got the sam result. I didn't att...

is there a way to crop a corner out of a rectangular image in Visi
Using subtract on two shapes accomplishes what i want to do, but when the first shape is a .gif image, it does not work. No, the "boolean operations", as they're called, do not operate on foreign objects like bitmaps and controls. Visio has crop functionality for imported graphics, but it is only rectangular. You could draw a small white triangle and give it "no line". Then place it over the corner of your bitmap. -- Hope this helps, Chris Roth Visio MVP "doug" <doug@discussions.microsoft.com> wrote in message news:29D479A6-6099-4051-B7A0-1...

Justifying 2-line category names in bar charts
When a category name wraps on to 2 lines, Excel centers the second line under the first. I'd like to figure out a way to right justify the second line. I'd also like to figure out a way to set that as a default. Hi, You do not have much control over the axis labels and their appearance. If you want full control you will need to use textboxes. This page has code to help with construction and alignment. http://www.andypope.info/vba/Axislabel_textboxes.htm Cheers Andy Camille wrote: > When a category name wraps on to 2 lines, Excel centers the second line under > the first...

Help with Coding about linked worksheet
I have a worksheet contains data, this data if for reasons I ente number ( 1 ) in a specific cells it will give the symbol ( *1 ) and th cell will turn to red color and if I enter the number ( 2 ) it wil give ( *2 ) and the cell will turn to blue color and so on for a tota of four different symbols and colors, And because of the limitation of the conditional formatting to onl three I�m using a code. Thanks, with the help of some guys in this site I got this cod working Private Sub Worksheet_Change(ByVal Target As Range) Set cRange = Intersect(Range("y11:y35"), Range(Target(1).Addr...

How to get list of namespces ?
How to get list of namespces that are declared in an element ? -- Best regards, Oleg Subachev subachev@ural.ru "Oleg Subachev" <oleg@urvb.ru> wrote in message news:OU4HcHWdEHA.2376@tk2msftngp13.phx.gbl... > How to get list of namespces that are declared in an element ? Given that you have first positioned yourself onto an XmlElement of an XmlDocument, one of the fastest ways is to grab an XPath- Navigator and enumerate the Namespace nodes that are present. XPathNavigator nav = myElement.CreateNavigator( ); if ( nav.MoveToFirstNamespace( ) ) do { ...

Selecting and moving chart(s) within a worksheet using keyboard ke
Hi all, I have created some charts in a worksheet. I would like to be able to do the following if possible: 1. I have selected a chart (by clicking on the chart and handles appeared around the perimeter of the chart). What I would like to do is to be able to select the chart using keyboard short-cut and to move the chart around the worksheet also using keyboard keys rather than the mouse. At the moment I can only move it (a single chart) using the mouse. However strangely enough, if I select two or more charts (Shift+ Lt.Click) I am able to then use the arrow keys to move the chart. ...

Is there a way to color code Outlook Contact File Folder Labels?
Is there a way to color code Outlook Contact File Folder Labels? No. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips mailing list: http://www.onenote-tips.net/ "isailhbca" <isailhbca@discussions.microsoft.com> wrote in message news:4DBD10F7-FAA0-4102-A4C3-E2697908EFE8@microsoft.com... >...

A way to create sub-categories?
I want to group contacts by category and then a sub-category for easy viewing. Apart from creating a new field for sub-category is there a way of doing this? Outlook doesn't seem to support sub-categories. Thanks Nope. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza http://www.cardozasolutions.com "Casebase" <junkmail@casebase.net> wrote in message news:%23rx5a...

I can't get my letterhead to only appear on the first page of a l.
I can't get my letterhead to only appear on the first page of a letter. I have tried using "different first page", but the letterhead keeps deleting from the first page, but stays on the remainder of the pages. What am I doing wrong. See http://word.mvps.org/FAQs/Formatting/NumberingFrontMatter.htm (for 2003 and prior) or http://www.word.mvps.org/FAQs/Formatting/front_matter_2007.htm for details on how to have different Headers and Footers, etc. in one document.Hope this helps DeanH "rlw" wrote: > I can't get my letterhead to only appear ...

customer on hold should get warning on recurring batch posting
If a customer is put on hold after a transaction for it is saved in a recurring batch, the system will still let the user post the batch without any warning. Please include the GP functionality to get a message prompt or warning that the user is posting a batch that includes transaction(s) for batch recurring. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion ...

Re: How can I stop Outlook to run subsequent rules from VBA function bound to currently running rule
And I thought to use Rule's olRuleActionStop in VBA. . Submitted using http://www.outlookforums.com ...

Store the full path and document name in a hyperlink
Hi I need to create an Access database to track a number of documents that are created and stored during the tendering process. I have created a number of tables with hyperlink fields that will store the information eg Tender proposal document (a Word doc); Tender budget document (an Excel book) and company profiles (pdf) from companies submitting tenders. To create the hyperlink to each of these documents in the form, at the moment I right click, choose Hyperlink, Edit Hyperlink and navigate to the required document and select it. The path and filename is then stored in the field, which is ...

How to Get Rid of Surfboard Shim Object View Message
I had to reinstall everything on my computer after a crash. I reinstalled my MS Money 2007. Now every time I click on a tab or want to do something I get this message about Surfboard Shim Object View. I've opened, saved, and cancelled it and it still keeps coming back. How do I get this to stop? Thanx In microsoft.public.money, Toni Black <Toni Black@discussions.microsoft.com> wrote: >I had to reinstall everything on my computer after a crash. I reinstalled my >MS Money 2007. Now every time I click on a tab or want to do something I get >this message about Surf...

When I type numbers in currency all I get is #######
After I type an amount in a cell, it turns to pound signs as soon as I go to the next cell. How can I fix this? Amanda Make the column wider -- Regards, Peo Sjoblom (No private emails please) "mommy4shane" <mommy4shane@discussions.microsoft.com> wrote in message news:6CAD94EE-EC45-4C10-9402-DBC94723CCD4@microsoft.com... > After I type an amount in a cell, it turns to pound signs as soon as I go > to > the next cell. How can I fix this? > > Amanda It could mean a few things. 1. The columnwidth is too narrow to show the number. Widen the colu...

Getting error in outlook
hi steve here i am getting error in outlook o/s : 98 Error: Unable to display the folder. Microsoft Outlook could not access the specified folder location. Errors have been detected in the file C:\WINDOWS\Local Settings\Application Data\Microsoft\Outlook\Outlook.pst. Quit all mail-enabled applications, and then use the Inbox Repair Tool. any help would be appreciated >hi steve here >i am getting error in outlook >o/s : 98 > >Error: >Unable to display the folder. >Microsoft Outlook could not access the specified folder location. >Errors have been detected in ...

changing extension names
I did try that. I can't remember the exact complication but it did not work. Want to quote your original post? -- Roady [MVP] www.howto-outlook.com Tips of the month: -Creating Signatures -Create an Office XP CD slipstreamed with Service Pack 3 ----- "Cap't Bob" <anonymous@discussions.microsoft.com> wrote in message news:1169501c441aa$e2f94810$a401280a@phx.gbl... >I did try that. I can't remember the exact complication > but it did not work. ...

Turn off gridline in partial Excel worksheet
I know how to turn off gridlines for an entire worksheet, but is it possible to turn off for just a portion of a sheet? I am pasting a link into PowerPoint of a partial worksheet, but it brings across the gridlines. Cannot just copy a picture, as I need the dynamic link for updating the data. Thanks for any ideas! Hi, it's impossible. You can only change formatting of selected cells to white color. "Queen Glitter Bunny" wrote: > I know how to turn off gridlines for an entire worksheet, but is it possible > to turn off for just a portion of a sheet?...

How do I get the gradient at a point on a curved graph?
I have a plot of experimental data and I inserted an exponential trendline that fits the data very well. I'd like to "differentiate" the curve as I need its gradient at certain points; as in I need Excel to draw the tangent to the trendline at a given point and tell me its equation. Can anyone help me do this? Thank you all so much. I don't think Excel knows how to do this as a built in thing. Extract the coefficients of the trendline to a worksheet (using a worksheet function such as LOGEST or a utility that will extract trendline coefficients to a worksheet). Then ...

is there a way to change the time card repor format?
i'm using activereports in RMS to generate a custome timecard report. for my timein and timeout colums, the datatype i have assigned to the columns is vbDate. when I generate the report, the columns only show the date as mm/dd/yyyy, they do not show the time of day piece for exactly when the cashier punched in or out. is there any way to change the formatting of this so that in the report the time shows up? for example, instead of showing 2/6/2005 in the time in column, it would show 2/5/2005 8:00:00 AM. thanks in advance, kevin There is a timecard report on the Reports Library...

#NAME and INDIRECT.EXT (broken?)
Cell looks like this =INDIRECT.EXT("'K:\CP\["&A4&".xls]Exam1'!$E$8") Cell A4 contains the string "test" and there is a file named "test.xls" When I completed the sheet this all worked. When I closed it and opened it again all I get are #NAME? errors. If I go into each cell (and there are hundreds of them) and "pretend" to edit the cell with the INDIRECT.EXT reference, then it works for that cell. What have I done wrong? Hi INDIRECT doesn't work with closed source file. When you open at same time the workbook which is...