Excel Text 3 of 4: Using SEARCH, MID & CELL to Display Sheet Name


Ask Quincy

Linking the worksheet name to a cell in the spreadsheet is easily accomplished using Excel’s CELL function. Once joined by the MID and SEARCH functions, you need only change the sheet’s name and the linked cell will update to match.

Click to view at full resolution Click to view at full resolution

Displaying the Sheet Name in a Cell

Type (or copy and paste) the following formula into a cell

=MID(CELL(“filename”),SEARCH(“]”,CELL(“filename”))+1,100)

Note

  • The file must be saved; the CELL function will not return a value on an unsaved file.
  • Press F9 (if necessary) to update the cell after a renaming a sheet.
MIDreturns a portion of text based on starting position and number of characters.=MID(“Task Quickly”,2,5) returns “ask Q”; starting with the 2nd char, 5 charachters long.
CELLreturns info about the cell, file or sheet.=CELL(“filename”) returns the path, filename and sheet.
E.g., C:[Balance.xlsx]Sheet1
SEARCHreturns  the number where the…

View original post 68 more words