allowing each value to be used once
 

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.
--
To those who have helped in the past and those about to help, Thank You.
There is an intregeing element to Excel that has captured me and that
stimulates my every remaining brain cell. Awesome

allowing each value to be used once
 

You can do this with data validation.

Assume the range of interest is A1:D1.

Select the *entire* range A1:D1 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 DataValidation
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

--
Biff
Microsoft Excel MVP


"Football Express" wrote in
message ...
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.
--
To those who have helped in the past and those about to help, Thank You.
There is an intregeing element to Excel that has captured me and that
stimulates my every remaining brain cell. Awesome

allowing each value to be used once
 

--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 FormatConditional Formatting

3. For Condition1Select '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 ButtonPattern and select your color (say Red)

5. Hit OK


If this post helps click Yes
---------------
Jacob Skaria


"Football Express" wrote:

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.
--
To those who have helped in the past and those about to help, Thank You.
There is an intregeing element to Excel that has captured me and that
stimulates my every remaining brain cell. Awesome

allowing each value to be used once
 

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

allowing each value to be used once
 

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

has already been used in the previous (up to 17) cell(s) in the SAME ROW

If this post helps click Yes
---------------
Jacob Skaria


"Bernd P" wrote:

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

allowing each value to be used once
 

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

allowing each value to be used once
 

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

allowing each value to be used once
 

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
--
To those who have helped in the past and those about to help, Thank You.
There is an intregeing element to Excel that has captured me and that
stimulates my every remaining brain cell. Awesome


"Bernd P" wrote:

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

allowing each value to be used once
 

Hello,

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

Regards,
Bernd

allowing each value to be used once
 

Didn't Work
Thanks to all in any case.
bigjim3762 at verizon net
--
To those who have helped in the past and those about to help, Thank You.
There is an intregeing element to Excel that has captured me and that
stimulates my every remaining brain cell. Awesome


"Bernd P" wrote:

Hello,

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

Regards,
Bernd