data validation across many worksheets

[Guest]

I have a workbook with multiple worksheets that are all identicable.

However, in each worksheet I have created a drop down data validation combo
box for the same specific cell in each worksheet. The data for the drop down
list is set once and every time I select from the drop down list in one
worksheet, the selected item should not be available in any of the other drop
down lists on any of the other worksheets.

I have seen a method that work when everything is only one worksheet and
works ok in that situation. However how do I do the same thing when the
references span across many worksheets, but the original dropdown list
resides in only one of the worksheets (although can be on all), but the
number of items available in the drop down list in each worksheet needs tor
educe by one evertime i make a selection

i cannot see how to have multiple worksheet references in the rnge for a
countif function
thanks

Pat

 

[Guest]

I forgot,

I am using excell 2000

 

[T. Valko]

I have created a drop down data validation combo box

Are you using a data validation drop down list or a combo box? They are 2
different things. I don't think you can do this using a combo box (at least,
not without VBA code or a whole lot of kludge).

There are instructions on how to do this with a data validation drop down
list here:

http://contextures.com/xlDataVal03.html

i cannot see how to have multiple worksheet references
in the rnge for a countif function

You have to wrap the COUNTIF inside of SUMPRODUCT.

To do a COUNTIF on cell AA1 across all sheets to see if any contain the
string "Bill":

List your sheet names in a range of cells. Assume this range is A1:A5

Then:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&A1:A5&"'!AA1"),"Bill"))