Help needed with unique numbers and multiple concatanated drop downsplease

[Potsy]

Hi

I have an excel sheet that has 4 drop down in Columns A,B,C and D
these are selected by user to generate a unique form number as
follows:

Column A: Department Code e.g. Sales = SA
Column B: Document Type e.g. Folder = FL
Column C: Form Number (1-99)
Column D: Revision Number (1-99)

I have concatenated in Column J to give 8 digit code above i.e. AA-BB-
CC-DD, however, need it to stop the user from entering and flag up if
that combination has already been used and reset. I have tried with
the following under validate > list > custom:

=COUNTIF($J$9:$J$202,J9)<=1

However, it only comes if I manually type value into column J and will
not work on a concatenated cell reference of A+B+C+D. Is there any way
to flag up when user selects drop downs and is referenced into Column
J (concatenated field).

Any help appreciated - i would sooner have drop downs to make it
easier than keying in code manually.

Thanks in advance.

Stuart

 

[Per Jessen]

Hi Stuart,

You can not use data validation on calculated values. However you can use
conditional formatting on calculated values, so you can make a cell change
color if the combinantion is used more than once with this formula:

=COUNTIF($J$9:$J$202,J9)>1

Hopes this helps.
....
Per

 

[Potsy]

Hi Stuart,

You can not use data validation on calculated values. However you can use
conditional formatting on calculated values, so you can make a cell change
color if the combinantion is used more than once with this formula:

=COUNTIF($J$9:$J$202,J9)>1

Hopes this helps.
...
Per

"Potsy" <[email protected]> skrev i meddelelsen









- Show quoted text -

thanks Per - a work around. is there any way to get a notification
message appear to highlight or reset fields to foolproof so that
cannot be booked out twice?

stuart

 

[Potsy]

Hi Stuart,

You can not use data validation on calculated values. However you can use
conditional formatting on calculated values, so you can make a cell change
color if the combinantion is used more than once with this formula:

=COUNTIF($J$9:$J$202,J9)>1

Hopes this helps.
...
Per

"Potsy" <[email protected]> skrev i meddelelsen









- Show quoted text -

thanks - just got it working, however, how do I add another rule to
conditional format to say if just first two field (column A) is filled
out then ignore as by default it will have this in e.g. SA-XX-XX-XX (x
= blank) for sales default.

 

[Herbert Seidenberg]

Excel 2007 Tables
Flag used drop-down combination.
With Sumifs() and CF
http://www.mediafire.com/file/mryznw4tnkd/02_09_10.xlsx

 

[Per Jessen]

Glad you got it working.

To stop this rule, just add this rule (the new rule has to be first), and
check 'Stop it this is true'. Format: No Fill

=counta(A99)<4

Regards,
Per