Compare 400 cells to one

[Bill Lyttle]

I'm using a drop down box that contains 1000 different choices. I'
using the same drop down box in 400 places on 8 sheets in a workbook.
want to make sure the same value is not used more than once. I've trie
usine the =if(or(F9=F11,F9=F13,F9=F15,F9=F17,F9=F19......)"Used"
"OK"). But it will not let me use more than 32 values. How do i compar
the 400 different cells to one to make sure the value is not picked mor
than once

 

[Teethless mama]

=IF(SUMPRODUCT((F11:F100=F9)*(MOD(ROW(F11:F100),2)=1))>0,"Used","OK")

 

[JLatham]

When you say that "I want to make sure the same value is not used more than
once." Do you mean more than once on a specific sheet, or not more than once
on any of the 8 sheets?

Toothless Mama has shown a possible way to test in one column on one sheet,
you could do that for all sheets, coming up with individual results and then
combining the results if you need to test for any multiple usage on multiple
sheets.

 

[Max]

One way to set it up for the DV control sought
Illustrated in this sample:
http://www.freefilehosting.net/download/3j2mb
Dynamic DV over several shts for unique selections.xls

In a sheet: DV,
Assume the DV items are listed in A2 down
Enter the source sheetnames in B1 across, eg: Sheet2, Sheet3
Assume the DVs are to be created in A2:A4 in each of the source sheets
Put in B2, copy across/filled down:
=COUNTIF(INDIRECT("'"&B$1&"'!A2:A4"),$A2)

Place in say, E2: =IF(SUM(B2:C2)>0,"",ROW())
Leave E1 blank
In F2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(A:A,SMALL(E:E,ROWS($1:1))))
Copy E2:F2 down. Col F will return the base list to be used for the dynamic
DV source

Create a dynamic defined range, say: MyR
to refer to:
=OFFSET(DV!$F$2,,,SUMPRODUCT(--(DV!$F$2:$F$6<>"")))

Now you can create the DVs using MyR as source in Sheet2's & Sheet3's A2:A4,
and you would have the exact control that you seek. Any DV items once
selected, will no longer appear/be availed for selection in the remaining
(unselected) DV cells in any of the source sheets. Test it out and see for
yourself. Extend to suit.

 

[Max]

Being a first time poster, it's important to start off right. This is a
discussion group, not a post-n-scarper. Do take the time to respond
individually to each response that you've received.

---