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

I forgot,

I am using excell 2000

"Patrick" wrote:

> 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

>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"))


--
Biff
Microsoft Excel MVP


"Patrick" <(E-Mail Removed)> wrote in message
news:0A712978-5178-4147-8034-(E-Mail Removed)...
>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