How to count # of worksheets?

S

Stephen POWELL

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
 
J

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
 
S

Stephen POWELL

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

Jason Morin said:
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
.
Click to expand...
Click to expand...
 
D

Dave Peterson

I think Harlan Grove suggested something like this to avoid that tiny caveat.

=IF(ISERROR(CELL("address",INDIRECT("'"&A1&"'!a1"))),"missing","Found")



Jason said:
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
.
Click to expand...
Click to expand...
 
S

Stephen POWELL

Dave:
I've done considerable work to incorporate Jason's suggestion into my
workbook. Thank you very much for your improving on his brilliance. I will
keep your/Harlan's approach in my records for use another time.
Thanks again,
Stephen

Dave Peterson said:
I think Harlan Grove suggested something like this to avoid that tiny caveat.

=IF(ISERROR(CELL("address",INDIRECT("'"&A1&"'!a1"))),"missing","Found")



Jason said:
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
.
Click to expand...
Click to expand...
Click to expand...
 
Top