Forum Discussion

JoelGDucharme's avatar
JoelGDucharme
Brass Contributor
Apr 24, 2024

List all sheet names in excel for the web

Hi, I have found a few ways to get a put a list of all the worksheet names using Excel desktop version. But, these use older formulas that activate macros and don't seem to work on the web version. A...
Excel for web
NikolinoDE's avatar
NikolinoDE
Gold Contributor
Apr 27, 2024

JoelGDucharme 

In Excel for the web, you can't use VBA macros or some of the advanced features of the desktop version. Unless you create the macro in a desktop version. However, you can still get a list of all sheet names using a formula.

=INDEX(CELL("filename",Sheet1!A1),1,LEN(CELL("filename",Sheet1!A1))-FIND("]",CELL("filename",Sheet1!A1)))

The formula has not been tried in Excel for web...or at all.

 

Hope it helps :smile:

  • peterrosenberg's avatar
    peterrosenberg
    Copper Contributor
    May 23, 2024

    NikolinoDE
    Interesting !
    Tried it out... Got a #REF.
    Instead, it looks like this would work:

    =RIGHT(CELL("filename";Sheet1!A1);LEN(CELL("filename";Sheet1!A1))-FIND("]";CELL("filename";Sheet1!A1)))


    Notice, in my region, we use semicolon as delimiter...

    Enjoy 🙂
     BR Peter Rosenberg

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      May 23, 2024

      peterrosenberg 

      Provided the CELL function works in Excel for the web, the formula can now be simplified by using 

      = TEXTAFTER(CELL("filename",A1), "]")

      Better still, by defining a 3D range

      home
= Jan:Mar!$A$1

= TOCOL(home)

      one can then create a table of contents

      [Note: These formulae are shown on the desktop version of Excel but I believe they read across]

  • JoelGDucharme's avatar
    JoelGDucharme
    Brass Contributor
    May 23, 2024
    Thanks, I didn't try it out because the person who needed this found another way to do what she was thinking.

Resources