Share via

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

Anonymous
2022-10-31T11:16:37+00:00

Hi,

Using excel 365 with an active work subscription.

The formula entered in a cell will write the tab name, when using the Excel 'app'.

But when opening the excel file through my.sharepoint.com (i.e excel online) the formula is broken.

Every time I open the excel file I have to 'enable external data connections' even though the excel file stored on onedrive. Maybe that is something to do with it?

If Cell Function doesn't work online, is there another way to extract the worksheet tab name into the worksheet?

Thanks for your support.

Microsoft 365 and Office | Excel | For business | Other

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

Answer accepted by question author

  1. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2022-11-01T12:01:10+00:00

    Assuming you never have apostrophes in the sheet names, you could add another SUBSTITUTE function:

    =SUBSTITUTE(SUBSTITUTE(LEFT(FORMULATEXT(B1),FIND("!",FORMULATEXT(B1))-1),"=",""),"'","")

    1 person found this answer helpful.
    0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-11-01T10:40:34+00:00

    I have made a opened a new worbook online, Sheet1 is blank. In Sheet2 I have entered the following.

    B1=Sheet1!$A$1
    C1=SUBSTITUTE(LEFT(FORMULATEXT(B1);FIND("!";FORMULATEXT(B1)));"=";"")

    Cell B1 pulls in 'nothing' from Cell A1 in Sheet 1. Cell C1 can't resolve the formula.

    See the screenshot below.

    0 comments
  2. Rory Archibald 18,885 Reputation points Volunteer Moderator
    2022-11-01T10:25:08+00:00

    B1 should contain a formula - including the sheet name - linking to a cell on the sheet in question. That way, if the sheet name is changed, the formula will automatically update. If you just put the sheet name in B1 then FORMULATEXT won't work and the cell won't update automatically if you rename the sheet.

    0 comments
  3. Anonymous
    2022-11-01T09:04:30+00:00

    Hi Hans,

    Thanks for the quick reply and for directly me to that techcommunity.

    That formula doesn't seem to work in our case.

    If we have to manually type the name of the worksheet into the formula of cell B1, why do we then need another formula to tell us what we have just written in the previous cell?

    Furthermore, when I paste the formula into cell C1, it comes up with a red dotted box around it and doesn't calculate.

    B1=Sheet1!$A$1
    C1=SUBSTITUTE(LEFT(FORMULATEXT(B1);FIND("!";FORMULATEXT(B1)));"=";"")

    Many thanks,

    Andy

    0 comments
  4. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-10-31T11:45:40+00:00

    The CELL function is not available in Excel Online.

    See the workaround by Bloggs20 near the end of the discussion at workbook and sheet name via formula

    0 comments