Help needed with unique numbers and multiple concatanated drop downsplease

  • Thread starter Thread starter Potsy
  • Start date Start date
P

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
 
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
 
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 -
Click to expand...

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
 
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 -
Click to expand...

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.
 
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(A9:D9)<4

Regards,
Per
 
Back
Top