Forum Discussion
workbook and sheet name via formula
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.
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 LambdaI'm trying for a solution that's backwards-compatible, eg. if you email the file to someone with Excel 365 (sadly, a common occurrence.)
I think I've found the glitch. It's not the FALSE command per se, it's that Excel inserts single-quotes when the sheet name begins with a number - which is my default, so larger workbooks are easier for users to navigate, especially if giving remote instruction over the phone or via email.If the active sheet name does not have spaces and does not begin with a number, then Excel does not require the single quotes and the OP's formula will return a sheet name without them.
For sheet names beginning with numbers, or those with spaces, the OPs formula will return the quotes around the sheet name.Hi MikeFromCanada 🙂 (I like the name).
If you want backward compatibility avoid Lambda expressions! Lambda is a new feature of the past 12 months or so. If you have someone with Excel 2019 or 2016 standalone version, Lambda does not exist. Excel 365 - do you mean the online version? For the online version / SharePoint you need a very different function, as CELL() does not work in those !
For the desktop Office 365 version, I have tried the:
=MID(CELL("filename",D1),FIND("]",CELL("filename",D1))+1,32)
on a sheet I named "3 Sheet", and that works. This formula will also work if you have 2016 or 2019 or 2022 stand-alone versions.
If you want to be versatile across Web / SharePoint and Desktop 365 or fixed versions then you need to use this:
=SUBSTITUTE( SUBSTITUTE( TEXTBEFORE( FORMULATEXT(D1), "!"), "'", "" ), "=", "")
where the cell content of D1 ='3 Sheet'!D2 , and the format of D1 is set to 0;0;
That solution is universal. It is a bit messy as it uses 3 cells. You can use the Formulas> Evaluate Formula function to check how it works. This solution was posted somewhere higher up in this thread too.
Happy days to you from New Zealand 🙂
