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.
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
I'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 🙂- 🙂 Fantastic, thanks - however, MikeFromCanada wants a function that works for all versions. TEXTSPLIT is new since 31 Mar 2022.If one just works in O365 then this is beautiful.