allowing each value to be used once

[Football Express]

Still looking for the same result!
Is there a function / formula that will RECOGNIZE if a 3 letter word (text)
has already been used in the previous (up to 17) cell(s) in the SAME ROW and
would, in some way (ANY way), alert the inputter that the word has already
been used.
The "word" is a 3 capitalized letter abbreviation of any one of the 32
football team towns abbreviated like CHI = Chicago, PHI = Philadelphia, and
so on) and the "row" or string will grow by one cell each Sunday.

 

[T. Valko]

You can do this with data validation.

Assume the range of interest is A11.

Select the *entire* range A11 starting from cell A1. Cell A1 will be the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula will be relative to the active cell.

Goto the menu Data>Validation
Allow: Custom
Formula:

=COUNTIF($A1:A1,A1)<2

You can have a custom message appear when a user tries to enter an invalid
entry.
Select the Error Alert tab. Fill in the info. OK out

 

[Jacob Skaria]

--Try the below conditional formatting which will highlight duplicates keyed
in the same row in the previous 17 cells to the left.

--Incase there are less number of cells to the left then it will check in
the available cells to the left.

--It will allow the 1st cell value to be entered again in the 18th cell

1. Select the rows to be formatted. say Rows 1 to 10. Please note that the
cell reference A1 mentioned in the formula is the active cell in the
selection. Active cell will have a white background even after selection

2. From menu Format>Conditional Formatting>

3. For Condition1>Select 'Formula Is' and enter the below formula

=COUNTIF(OFFSET($A1,0,IF(COLUMN()>17,COLUMN()-17,0),1,IF(COLUMN()>17,17,COLUMN())),A1)>1

4. Click Format Button>Pattern and select your color (say Red)

5. Hit OK


If this post helps click Yes

 

[Bernd P]

Hello,

With Excel 2007 the conditional format is even easier:
Goto Home / Conditional Formatting / Highlight Cells Rules / Duplicate
Values ...
.... indicating that a conditional format might be preferrable to a
data validation.

Regards,
Bernd

 

[Jacob Skaria]

Thanks Bernd. I dont think the OP is looking for just duplicates; or
otherwise the OP need not mention the below

If this post helps click Yes

 

[Bernd P]

Hello Jacob,

If the OP would not like to highlight the first instance then I would
simply suggest
=COUNTIF($A1:A1,A1)>1


Regards,
Bernd

 

[Bernd P]

Hello Jacob,

Of course you are right if the OP really wants to restrict the
highlighting to the previous 17 cells (current included) of the same
row...

Regards,
Bernd

 

[Football Express]

Guess I got lost in the translation of the formulas as I was not able to make
any of them work.
Columns P6 thru AF6 have the chosen team of each week automatically placed
into them via =T formula. U6 thru AF6 remain blank as those weeks have not
been played yet. Any formula or validation can be placed anywhere, I was
trying to put your suggestions into AG6. There is a different player in rows
6 thru 39 and I assume if I can get row 6 to work I can simply paste the
formula from row 6 down to the others.
Thankx Again

 

[Bernd P]

Hello,

Select P6 first, then select all cells from P6 thru AF6 and enter into
your conditional format: =COUNTIF($P66,P6)>1

Regards,
Bernd

 

[Football Express]

Didn't Work
Thanks to all in any case.
bigjim3762 at verizon net