How to put the total number of sheets in a work cell?
Thanks

One way...

Create this defined name...
Goto the menu InsertNameDefine
Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())
OK

Then, to get the count of worksheets:

=COUNTA(INDEX(SheetNames,0))

--
Biff
Microsoft Excel MVP


"booshi" wrote in message
...
How to put the total number of sheets in a work cell?
Thanks

hi
i don't think there is a function that will do that. there used to be a call
function but that was removed in xl2k (i think) due to security issues.
here is a vb solutlions that works
Sub countworksheets()
Range("A1").Value = ActiveWorkbook.Sheets.Count
End Sub

paste the macro in a standard modual.

regards
FSt1

"booshi" wrote:

How to put the total number of sheets in a work cell?
Thanks


.

To count the number of sheets in an Excel workbook:

1. Use the formula:
   Formula:

   =COUNTA(INDIRECT("1:"&SHEETS())) 

2. The INDIRECT function creates a reference to all the sheet names in the workbook.
3. The COUNTA function counts the number of non-blank cells in that reference.
4. The SHEETS function determines the total number of sheets in the workbook.

To display the total number of sheets in a different cell:

1. Reference the cell containing the above formula.
2. Use the formula:
   Formula:

   =A1 

3. This will display the same number as the previous formula, but in a different cell.
4. Format the cell to display the number as text, if desired, by selecting the cell and choosing "Text" from the "Number Format" dropdown in the "Home" tab of the ribbon.