How to count # of worksheets?

[Guest]

I need a way to count the # of worksheets in a workbook.
Does anyone know how to do this using a formula?
(I need to be sure of the completeness of my workbook; more specifically,
the name of each worksheet is a five digit number that corresponds to a
vehicle, and on a separate Summary worksheet I will have a list of all the
vehicles [approx 100] and I want a quick way to be certain that I have the
same # of worksheets as # of vehicles.)
Thanks,
Stephen Powell

 

[Jason Morin]

Here's an idea. If your 5 digit vehicle numbers are
listed in A1:A100, put this in B1:

=IF(ISERROR(INDIRECT("'"&A1&"'!IV65536")),"missing","")

and fill down to B100. Then in another cell (say C1) use:

=COUNTIF(B1:B100,"missing")

This way will help you quickly identify how many sheets
are missing and which ones.

The only "tiny" caveat is that you can't have an error
value in IV65536 in any of your sheets.

HTH
Jason
Atlanta, GA

 

[Guest]

Jason:
Your suggestion is perfect. Thank you.
I tested it and it works. I figure that I can also use it to test for the
presence of wrong codes because these vehicle codes are not sequential but
cover a range of about 1000 from beginning to end. By using 1,000 rows in a
worksheet I can test for the presence of old codes that should have been
deleted when the asset was disposed of. You've made my day.
Stephen

Here's an idea. If your 5 digit vehicle numbers are
listed in A1:A100, put this in B1:

=IF(ISERROR(INDIRECT("'"&A1&"'!IV65536")),"missing","")

and fill down to B100. Then in another cell (say C1) use:

=COUNTIF(B1:B100,"missing")

This way will help you quickly identify how many sheets
are missing and which ones.

The only "tiny" caveat is that you can't have an error
value in IV65536 in any of your sheets.

HTH
Jason
Atlanta, GA

-----Original Message-----
I need a way to count the # of worksheets in a workbook.
Does anyone know how to do this using a formula?
(I need to be sure of the completeness of my workbook; more specifically,
the name of each worksheet is a five digit number that corresponds to a
vehicle, and on a separate Summary worksheet I will have a list of all the
vehicles [approx 100] and I want a quick way to be certain that I have the
same # of worksheets as # of vehicles.)
Thanks,
Stephen Powell
.