Data Validation to Ensure Like Numbers in Cell A has Same Date in Cell B

[Gos-C]

Hi all,

I am trying to set up a data validation to ensure that when a user enters a number in column A that matches a previously entered number in column A, the user is only allowed to enter the same date for the number it matches in column B.

For example, if:

Row 3 column A = 1320

and

Row 3 column B = 16-Dec-07

and

Row 15 column A = 1320

then:

Row 15 column B must be 16-Dec-07

Any help?

Thank you,
Gos-C

[VBA Noob]

If you want to get a workable answer you need to add more info as I assume you have more than one number and one date.

Also how do we know what the link is between your number and the date ??

VBA Noob

[Gos-C]

Hi VBA Noob,

Column A contains job numbers and column B contains the dates when the jobs in column A were created. The job numbers are entered several times and, in order to have the correct result from formulas, the like job numbers must have the same dates.

HTH

Thanks,
Gos-C

[NBVC]

Select your range starting at cell B2 and in the Data Validation window, select Custom from the Allow section and enter formula: =B2=LOOKUP(2,1/($A$1:A1=A2),$B$1:B1)

click Ok.

[Gos-C]

Hi NBVC,

It works only when all the numbers are the same.

Gos-C

[NBVC]

Not sure what you mean. Please elaborate.