Forum Discussion
workbook and sheet name via formula
I have a standard header I put on almost every excel workbook I create I have been using for years. It includes several standard items, including Workbook name and Worksheet name, which are extracted via CELL("Filename",A1) formula.
For Excel workbooks that are being viewed in Excel Online, however, whether through onedrive, sharepoint, or whatever, this formula yields a #VALUE! error. When I open in desktop, it renders fine, just not when looking at browser window.
Is there an alternative that I can use to display current workbook and worksheet names via a formula in a cell that is compatible with Excel Online?
Afraid for Excel Online that's only with Office Script, there are properties getName both for workbook and worksheet.
VBA and related functions don't work in Excel Online.
63 Replies
This is the most recent method I was able to get working.
The only drawback is that changes to sheets need can only update after refreshing the query in the desktop app, but it does leave room for someone more capable with VBA and OfficeScripts/TypeScript.I also had a thought that Fabric/PowerBI connections might help with compatability... ANYWAY!
Important: Must be done in desktop app at this stage but the links persist online
Stage 1Data > Add Source > from Web > Sharepoint file URL (from details tab in SharePoint online NOT from URL bar) > Use 'Organization Login' option to authenticate
Stage 2
R-Click top folder & select Transform Data > filter down Columns to Name & Hidden (if you want to filter out hidden tabs) with sheet name & Hidden yes or no
Stage 3
Save and Load Data (Renaming the query first will make it easier to reference)
Stage 4
On the newly created sheet with your table, add column on the end of the table called "Links" > first cell formula below
=HYPERLINK("#'"&[@[Name]]&"'!A1","Go to Sheet")
Probably have a couple bits missing, but i can clarify if people ask.
- As stated before that doesn't work in the Web / SharePoint version 🙂
Hi, I don't know if you ever solved your problem. I hope you did, but in case you didn't, this was my jurney to solve it.
I have now a working sollution for both web and non web Excel spreadsheets, unfortunatly the Formula it's in Portuguese. Most probably you won't understand it, but I put it here anyway. Maybe from one you get the other.
The basic thing you need to understand is that, the same formula in Excel offline and in excel online gives different result's. For this porpous A1 = "=Sheet1!A1"
The "FORMULATEXT(A1)" formula offline result in ='Sheet1'!A1 and online result in =Sheet1!A1 (exactly), so if you try to look for "'" online, it will give an error, but not offline, so this is usefull to use IFERROR() function, wish solved both problems.
So, in general, offline you look for "'" and online you look for "!" (both are arround of what you want to know), first I looked for the Left Text of those, and after I took the right text except 1 or 2 chars.
My Formula (in portuguese excel) is this one:
=SE.ERRO(DIREITA(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1);NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1))-2);DIREITA(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1);NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1))-1))
Trying to decompress it looks like this:
=SE.ERRO( #first part of error will work offline but not online
DIREITA(
ESQUERDA(
FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1
) #end of left
;
NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("'";FÓRMULA.TEXTO(A1);3)-1))-2
) #end of right
; midle of iferror (second part work online)
DIREITA(
ESQUERDA(
FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1
) #end of left
;
NÚM.CARAT(ESQUERDA(FÓRMULA.TEXTO(A1);PROCURAR("!";FÓRMULA.TEXTO(A1);3)-1))-1
) #end of right
) #end of iferror
Translation of formulas probably looks like this (not sure..)
all ; must change to ,
SE.ERRO = IFERROR
DIREITA = RIGHT
ESQUERDA = LEFT
FÓRMULA.TEXTO = FORMULATEXT
NÚM.CARAT = LENGHT
PROCURAR = FIND
(the main diference are the number of arguments that each function has in both languages, so you need to account for that.)
Copilot gave me this translation result:
To translate the Portuguese Excel formula to English, you can use the following formula:
=IFERROR(RIGHT(LEFT(FORMULATEXT(A1),FIND("'",FORMULATEXT(A1),3)-1),LEN(LEFT(FORMULATEXT(A1),FIND("'",FORMULATEXT(A1),3)-1))-2),RIGHT(LEFT(FORMULATEXT(A1),FIND("!",FORMULATEXT(A1),3)-1),LEN(LEFT(FORMULATEXT(A1),FIND("!",FORMULATEXT(A1),3)-1))-1))This formula performs the same operations as the original Portuguese formula. It extracts the relevant portion of the formula based on the positions of single quotes (') and exclamation marks (!) within the text contained in cell A1.
Please note that this is an approximation, and it may not work correctly if the formula structure is different or if there are other special characters involved.
Hope it works for you! and thanks for reading untill here 🙂
Looking for single quotes won't work in all cases. I found out just last week that Excel only requires the single quotes for sheets with spaces or that begin with numbers.
Best I've been able to do is:
IFERROR( [formula that works other than Excel Web App] ,
"Formula doesn't work in Excel for the Web").
Admittedly it doesn't solve the Web problem, but at least tells the user what's going on...
Addendum: REALLY impressed with your response generally, let alone that you translated it into a second language.
- The best solution which has been listed high above y someone else remains:
=SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D1), "!"), "'", "" ), "=", "")
With D1 containing e.g.:
='3 Sheet 3'!D2
This works for sheet names like "3 Sheet" or "Sheet 3" or "Sheet3" And resolves the single quotes or not single quotes.
Very simple, very elegant
- Just in case anyone else was after a formula using standard excel functions to copy paste and get the sheet name: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))+1)
An alternative to fetch sheet name (365, desktop version only):
=TEXTAFTER(CELL("filename"),"]")Wow Patrick, very elegant. However that doesn't work in Excel in SharePoint.
This thread mainly about Excel for web where such formula doesn't work.
With Excel 365, this can be beautified by creating a defined name in workbook scope:
Name:
GetSheetName
Refers to:
=LAMBDA(refCell; LET(frmTxt; FORMULATEXT( INDIRECT( ADDRESS(ROW();COLUMN()) ) ); SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt;"!"); "("; -1); "'"; "") ))Then, you can use a simplified form in your worksheets:
=GetSheetName(Sheet1!A1)
Again, you have to add the name of the sheet in the cell reference manually if you want to retrieve the name of the same worksheet. After that, it is automatically updated if you rename or duplicate the worksheet.
GeertCL that only returns 0
Hi Guy,
There must be something specific about my Excel settings that differs in your case.
As you can see in the above screenshot, I have the expected results on the web browser-based version of Excel Online, both for the name of the same worksheet as for another worksheet.
However, I did notice that one cannot create the defined name in the web browser, only use it. You have to open it with the Excel app to have access to the Name Manager.
Also, in my localisation, the separator is a semi-colon, not a comma.
Unfortunately, some testing and debugging will be necessary in your case.
Row 4 shows how you can use the LAMBDA function directly, without defined name: note that you have to add the ('Sheet1 Copy'!A1) argument at the end.
=LAMBDA(refCell, LET(frmTxt, FORMULATEXT( INDIRECT( ADDRESS(ROW(),COLUMN()) ) ), SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt,"!"), "(", -1), "'", "") ))('Sheet1 Copy'!A1)As the result shows, this is fooled by the preceding exclamation mark that appears in the TEXTBEFORE function.
For testing, one can adapt the TEXTBEFORE function to search for the exclamation mark in reverse direction with the -1 value in the third argument (row 5):
=LAMBDA(refCell, LET(frmTxt, FORMULATEXT( INDIRECT( ADDRESS(ROW(),COLUMN()) ) ), SUBSTITUTE(TEXTAFTER( TEXTBEFORE(frmTxt,"!",-1), "(", -1), "'", "") ))('Sheet1 Copy'!A1)
Actually, one can do it without VBA or hidden sheets using the following formula:
=IF(FALSE;'Sheet1'!A1;MID(FORMULATEXT(INDIRECT(ADDRESS(ROW();COLUMN())));11;FIND("!";FORMULATEXT(INDIRECT(ADDRESS(ROW();COLUMN()))))-11))In this formula, you replace the 'Sheet1'!A1 by the reference to the first cell in the sheet whose name you want to show. It can be the current sheet, but then you have to add the sheet name once, manually. If you are lazy like me, you can also enter this formula temporarily on another sheet, use point-and-click to the current sheet to insert the reference, and then copy the formula over.
The formula exploits the fact that it uses its own text, and that the prefix is known ("=IF(FALSE;" - 11 characters). That is where the ADDRESS/ROW/COLUMN construct comes in: that is how the FORMULATEXT gets the formula of the current cell, while referencing another cell.
If you rename the sheet, or copy it as a duplicate sheet, the reference updates automatically.
This is the basic version of the formula. The result will still contain the single quotes if the sheet name contains blanks. You can surround the formula with a SUBSTITUTE(formula;"'";"") construct to get rid of those.
In Excel 365, this can be simplified with the TEXTBEFORE and LET functions, and you can create a GetSheetName(reference) function-like defined name that uses the LAMBDA dynamic function.
Hoping for your help:
Your first formula works in Excel 365 on the web or the desktop app (you used semicolons/ I've restated for the North American comma convention):
=IF(FALSE, 'Sheet1'!A1, MID(FORMULATEXT(INDIRECT(ADDRESS(ROW(), COLUMN()))), 11, FIND("!", FORMULATEXT(INDIRECT(ADDRESS(ROW(), COLUMN()))))-11))
returns
'100_Core_CY' You'll note that I replaced the blanks in my sheet name with underscores, yet your formula still returns the single quotes on either side of the sheet name.
I tried to remove them using your SUBSTITUTE suggestion, but that didn't work. It appears to interfere with the FALSE command:
=SUBSTITUTE( IF(FALSE,'100_Core_CY'!$A$1,MID(FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))),11,FIND("!",FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))))-11)) ,"'","") [that last bit is comma, double-quote, single-quote, double-quote, then replace with two double-quotes, i.e. null.]
returns
E(IF(FALSE,100_Core_CY I'd be grateful if you could take a moment to explain where I might have gone wrong.
Many thanks!
Dear MikeFromCanada , you didn't address this to me so I'll happily butt out; still, why are you trying to make it so complicated?
Perhaps you wish to use a Lambda function to only have one parameter?This is the shortest function that just works:
=MID(CELL("filename",D1),FIND("]",CELL("filename",D1))+1,32)where d1 contains a reference like =Sheet1!D2, with formatting set to 0;0; The easiest to create that reference is to type (in D1) = , select another sheet, then select Sheet1 again and click on D2.
The trick to avoid issues and improve maintainability is to keep it simple?!
Before you create the Lamba, put your formula in a cell, then use the Formulas > Evaluate formula function to step through what is going wrong. Once debugged, turn it into a Lambda
- The solution of GeertCL was the most workable for me?
Thanks to the help above the following formula worked for me.
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))CELL("filename",A1) returns the full path filename and worksheet name with the work book in square brackets []
LEN (string) gives the length of a string
FIND (string1, string2)returns the position from the left of the first occurrence of string1 in string2.
RIGHT(string ,n) gives the last n characters counting from the right.
Hi Everyone,
My workaround for returning the worksheet cell/range reference in Excel Online is as follows:
Pick an input cell where you will reference a cell or range. In this case it is D2.
In another cell (in this example I use A2), paste the following formula:
=REPLACE(FORMULATEXT(D2),1,1,"")
This formula turns the formula in D2 into a text string and then replaces the equals sign in the first character with "", removing it from the string.
In D2, type the equals sign, then use your cursor to select the cell or range you need the reference for and hit return. A2 should now display the reference of the range/cell added to D2.
This works across sheets and probably across workbooks with the correct permissions.
I am personally using it as a way to expedite modification of a 3rd formula.
In this 3rd formula, I am replacing range/cell references with a reference to what appears in D2, but enclosed in the Indirect function:
Indirect(D2)
This will take the string in D2 and treat it as a cell reference again.
Now I am able to replace the cell/range references in the formula by inputting them into other cells rather than changing the formula itself.
- Strange thing is that it works momentarily when you open the spreadsheet, displaying the correct value, before reverting to #VALUE!
If you had some value in last saved in desktop version file, it is shown in Online version from cache till you recalculate worksheet. After that an error.
Does Office 365 Excel support VBA?
All versions of Windows desktop Excel from Excel 5 through Excel 2019 support VBA.
This includes the version of Excel installed by Office 365. ...
There is no version of Excel Online, iOS Excel or Android Excel that supports VBA.
Nor does any version of Excel that runs on Windows RT support VBA.
In my humble opinion, it would be good to always have the possibility of VBA in Excel.
All new options such as editing PDF and cloud options, in all future versions, will be require VBA.
Unless someone has already adjusted to Java script ... which I don't hope.
What I don't know is, if you maybe can open from the Developer tab in Visual Basic editor, I don't have Office 365 yet. You can try it, maybe…maybe it will work.
The sequence is in Excel 2016
Click File
Click Options
Click Customize Ribbon
Under the list of Main Tabs, select Developer
Click OK
The Developer tab will now appear on the Ribbon and from it you can open the Visual Basic Editor.
If opening the Developer tab and clicking a button is a little too much work, you can also open the editor with the keyboard shortcut Alt+F11, which works whether the Developer tab is enabled or not.
As I told you before, with the logic of Excel 2016.
Nikolino
I know I don't know anything (Socrates)
- yeah, VBA is supported in O365 - this question is specific to Excel documents being intereacted with through a web browser (Excel online) though.
The CELL function which works on desktop does not work with online (weirdly), and same is true of VBA.
It's a pretty small detail (CELL formula not working online), not a critical functionality, just annoying to me, because I literally use the same header on every document as a matter of best practice.- you may always check if function available for online version or not here Excel functions (alphabetical) In particular, what it says for CELL
- you may vote for the idea to add CELL() for the online version here CELL function added to online . In theory more votes more chances it'll be added.
- Perhaps one day functionality of desktop and online versions will be synced, at least that's how I understand Microsoft intention. But with that most probably not VBA will go online but Office Script will go on desktop. That's a long way.
Read out the designation / name of the worksheet
Read out by formula
The focus of the formula solution is the CELL function (infotype; reference). In addition to the file name and the full path, the File Name infotype also returns the name of the worksheet. This is extracted using further functions so that only the sheet name is output at the end.
The result is achieved with these two formulas:
(formulas are from German Translate)
a) = PART (CELL ("filename"; A1); FIND ("]"; CELL ("filename"; A1)) + 1; 255)
or
b) = PART (CELL ("filename"; A1); FIND ("]"; CELL ("filename"; A1)) + 1; LENGTH (CELL ("filename"; A1)) - FIND ("]"; CELL ("filename"; A1)))
2. Read out via VBA
The following VBA function reads out the sheet name via VBA and displays it in a cell. To do this, enter the following code in a code module.Public Function sheetname () As String Application.Volatile sheetname = ActiveSheet.Name End function
If you now enter the function = sheetname () in a cell, the sheet name is read out and output in the corresponding cell.I would be happy to know if I could help.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
works great but for active worksheet only..actaually i want sheetname in all sheet with its own sheetname.　Thank youNikolinoDE
rcstha - you just need to put the formula on each sheet. Just select which environment your workbook has to work in: Desktop or SharePoint/Web.
Desktop:
=MID(CELL("filename",'Sheet1'!A2),FIND("]",CELL("filename",'Sheet1!A2))+1,32)
(thanks NuggetSA )SharePoint/Web:
=SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D2), "!"), "'", "" ), "=", "")
with D2 containing:
='Sheet1'!D1 formatted as 0;0;
The latter is easy to create: place your cursor on D2 type = switch to another sheet, then switch back to Sheet1, and click on D1. Done.
I guess the German translation wasn't too good. Try this:
=MID(CELL("filename",'Sheet1'!A2),FIND("]",CELL("filename",'Sheet1!A2))+1,99)
The MID() formula picks an extract from CELL("filename") starting at the FIND() position for the "]" and returning whatever is to the right. If you look at what =CELL('filename",<location>) returns it will help visualise.
The 99 at the end is a lazy parameter assuming there is no more than 99 characters after the "]" was found. It's probably safe but you can increase if you want to or make the formula more complicated by calculating the remaining length. I don't think the additional technical artistry helps understand the formula!
NuggetSA - nice simplification, 99 can be reduced to 32 as that is the maximum length of a sheet name. But this only works in the desktop version not the SharePoint or Web instance.
NikolinoDE Before moving to Office 365 I used this function to include the workbook name in the Heading of the Workbook (not in the header) so new versions automatically pick up the right name. It almost works in 365, using =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-6) but as soon as I open a 2nd workbook it changes the content of the cell in the 1st workbook to the filename of the newly opened workbook. Very frustrating and confusing!
You may vote here CELL function added to online for this function. More votes sooner it could appear.
