Share via

Referencing a spreadsheet tab title in a cell

Anonymous
2013-09-19T05:03:41+00:00

I was wondering if there was a way the a cell withina a spreadsheet can reference the title from a tab for a spreadsheet? In otherwords, Lets say I open a sheet and want cell B2 to "mirror" the title on the tab for that sheet without having to changing the title on the tab then going to cell B2 to do it again. If there a function in a formula or a macro I can use?

On the flip side. Can a spreasheet tab have a cell reference where the what ever the data is in the a referenced cell appears on the tab as a title?

Thank you for yout time on this matter.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

25 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2013-09-19T05:15:54+00:00

    Try something like this:

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

    100+ people found this answer helpful.
    0 comments
  2. Anonymous
    2013-09-21T16:56:29+00:00

    The "]" in the above formula should not cause a problem.  In fact it is exactly what is used in all the other solutions.

    If you want a title for just one sheet then this is a satisfactory solution provided you 1. stay on that sheet, or 2. recalculate when returning to the sheet, since the formula uses the last sheet you were on when Excel recalculated.  The first formula will automatically update to the current spreadsheet when the spreadsheet recalculates - Shift+F9 or F9.

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

    This is because CELL is a volatile function, one that recalculates every time Excel updates and without a reference argument, the function calculates for the currently active sheet. 

    by adding the reference argument to the CELL function you can place the formula on more than one sheet getting different title for each:

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

    One other point, the last argument is set to 31 rather than 255 because the maximum number of characters in a sheet name is 31, not 255.

    40+ people found this answer helpful.
    0 comments
  3. Héctor Miguel 71,595 Reputation points
    2013-09-21T18:04:36+00:00

    hi, all !

    just to add my $0.02...

    "the workbook" MUST be saved in order to =CELL(... function works (?)

    hth,

    hector.

    10+ people found this answer helpful.
    0 comments
  4. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2013-09-20T00:00:28+00:00

    Hi,

    You may refer to my solution at the following link - http://www.ashishmathur.com/extract-tab-name-in-cell/

    Hope this helps.

    10+ people found this answer helpful.
    0 comments
  5. Anonymous
    2013-09-21T19:06:34+00:00

    Hi Hector,

     

    No, saving is not required.  What you are seeing with save is that Excel recalculates the spreadsheet prior to saving and that updates the CELL function as discussed in my previous post.

    Shane

    I do not run 2010 but in all earlier versions you will get the #VALUE! error if workbook is New and has not yet been saved.

    Gord

    9 people found this answer helpful.
    0 comments