validation
 

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

However, in each worksheet I have created a data validation combo for the
same specific cell in each worksheet. The data for the validation 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 works when everything is only on 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 to
reduce by one evertime i make a selection

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

Pat

validation
 

Since you show nothing specific, then the best I can say is you can have
multiple countif statements. There is an array formula using Indirect that
allows a countif across multiple sheets

=SUMPRODUCT(COUNTIF(INDIRECT({"Sheet1","Sheet2","S heet3"}&"!A1:A10"),2))

I don't know if that helps.
--
Regards,
Tom Ogilvy


"Patrick" wrote:

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

However, in each worksheet I have created a data validation combo for the
same specific cell in each worksheet. The data for the validation 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 works when everything is only on 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 to
reduce by one evertime i make a selection

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

Pat