We can easily retrieve sheet name by following – Tips & Tricks 34 – Get Sheet Name through Formula
But the formula depends upon CELL function. Excel Online i.e. web version of Excel doesn't support CELL function. It doesn't support VBA as well. Hence, we need to find an alternative to extract sheet name through some other trick.
- Insert a new sheet (or you can utilize an existing sheet as well). Optionally, you can also hide this sheet later on. Let's assume that this new sheet is Sheet2.
-
Let's say you want to retrieve the name of a sheet whose name is "Test Sheet".
In the new sheet, you can put following formula
='Test Sheet'!1:1
Now, let's assume that you put this formula in A2 of Sheet2.
Now, in first sheet you can use following formula to retrieve the sheet name
=SUBSTITUTE(MID(FORMULATEXT(Sheet2!A2),2,FIND("!1:1",FORMULATEXT(Sheet2!A2))-2),"'","")
The sample file demonstrating this is @ CELL_Excel_Online
Actually, you can replace ='Test Sheet'!1:1 with any formula referring to sheet whose name you want to retrieve. It can even be say ='Test Sheet'!A1
Then in second formula, you would need to replace ='Test Sheet'!1:1 appropriately.
-
In case, you want to do this in multiple sheets – hence, if you need to retrieve the names in 5 sheets, you will need to create 5 formulas
So, in a sheet, you will need to put formulas like in different cells say A2, A3, A4, A5 and A6.
='SheetA'!1:1
='SheetB'!1:1
='SheetC'!1:1
='SheetD'!1:1
='SheetE'!1:1
Now, you will need to put in Sheet2 following formulas(I am just replacing A2 with A3, A4…A6)
=SUBSTITUTE(MID(FORMULATEXT(Sheet2!A2),2,FIND("!1:1",FORMULATEXT(Sheet2!A2))-2),"'","")
=SUBSTITUTE(MID(FORMULATEXT(Sheet2!A3),2,FIND("!1:1",FORMULATEXT(Sheet2!A3))-2),"'","")
=SUBSTITUTE(MID(FORMULATEXT(Sheet2!A4),2,FIND("!1:1",FORMULATEXT(Sheet2!A4))-2),"'","")
=SUBSTITUTE(MID(FORMULATEXT(Sheet2!A5),2,FIND("!1:1",FORMULATEXT(Sheet2!A5))-2),"'","")
=SUBSTITUTE(MID(FORMULATEXT(Sheet2!A6),2,FIND("!1:1",FORMULATEXT(Sheet2!A6))-2),"'","")