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.

[Gos-C]

Hi NBVC,

Take a look at the attached file, and re-enter the dates shown so that you could see where the data validation is not working.

Thanks,
Gos-C

[NBVC]

This seems to work:

Select your range starting at cell B2 and in the Data Validation window, select Custom from the Allow section and enter formula:

Please Login or Register  to view this content.

[Gos-C]

Hi NBVC,

It didn't work. I was able to enter any date for all the numbers.

Gos-C

[DOES]

Hello Gos-C!

It seems any how you're going to manually enter very first value and then only you wanted to match the same value if the data you're entering also same. As data validation feature only validate pre-set values, I've some tricky vlookup function which will pull the date if any you've already entered in Column "B" upto previous row, then produce a match!

The code:

=IF(ISNA(VLOOKUP(a2,$a$1:b2,2,0)),"Enter your date",TEXT(VLOOKUP(a2,$a$1:b2,2,0),"dd/mm/yy"))

Give a try!

DOES

[Gos-C]

Hi DOES,

Thanks for your reply. I can't get it to work. Did it work for you? Where am I putting the code (in the Data Validation window)? I am getting an error for any date I enter in any cell from B2 down.

Gos-C

[NBVC]

Hi NBVC,

It didn't work. I was able to enter any date for all the numbers.

Gos-C

It's working fine for me....see attached.

Enter dates in cells B2 down....

When you come to B4, if the date doesn;t match what's in B2, you get an error, but if it matches, it is accepted.

Does it not work for you?

[Gos-C]

Hi NBVC,

Yes, it works - great! I must had done something wrong.

Thanks a million.

Gos-C