Data Validation and COUNTIF

  • Thread starter Thread starter Katherine
  • Start date Start date
K

Katherine

I've created a workbook where each sheet holds
information about the specification of a particular
computer. I want to create a summary sheet that will
count how many of the computers have, for example, XP as
their operating system.

The cell reference for the operating system is the same
in each sheet (I20) so i've created a named range OSall
sheets which refers that cell in all sheets in the
workbook (but not to the summary sheet) --> '111ad:111ab'!
$I$20

On the summary sheet, i have the formula =countif
(osallsheets,"XP") which i was hoping would return the
number of sheets in that range that have XP in cell I20.

It doesnt. I'm just getting a #VALUE! error.

One thing that may affect it: one each sheet i have data
validation set up so that in cell I20 for example, you
are forced to chose the operating system from a pulldown
list (xp, 2000 etc) - is this where i'm going wrong or
have i messed up the formula?

Does anyone have any suggestions as to how i can correct
this...or a better way to summarise the information?

Thank you!
 
Excel is not very robust nor flexible when working with 3D
ranges. I suggest you list your worksheets names in col. A
on a summary sheet and computer characteristics across the
top. Something like:

A B C D
1 PC OS RAM Model
2 111ad
3 111ac
4 111ab
..
..
..

Then in B2, put:

=INDIRECT(A2&"!I20")

and fill down. Change I20 for other characteristics. Then
you can more easily do your COUNTIF's on this sheet.

HTH
Jason
Atlanta, GA
 
Back
Top