Excel – Formula to get total number of worksheets

by

I have 100+ sheets, it is hard for me to count them one by one.

Is there a one line formula to get the total number of sheets? The CELL or ADDRESS function perhaps?

I remember using something like this once, but I cannot seem to remember it.

Thank you.

Answer

Here is a neat, formula-based solution.

  1. Create a New Name in Name Manager called wshNames, with the value:
    =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))

  2. In any cell you want to show the number of worksheets, enter: =COUNTA(wshNames)

enter image description here

Note: Step 1 relies on an XLM formula. More on this here – note you will need to save as a .xlsm. This technique is borrowed from David Hager, via this.

Attribution
Source : Link , Question Author : user338420 , Answer Author : Community

Leave a Comment