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

**NBVC** · 01-10-2008, 08:39 PM

Not sure what you mean. Please elaborate.

**Gos-C** · 01-11-2008, 09:46 AM

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** · 01-11-2008, 03:31 PM

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:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),TRUE,B2=INDEX($B$1:B1,MATCH(A2,$A$1:A1,0)))

**Gos-C** · 01-11-2008, 08:20 PM

Hi NBVC,

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

Gos-C

**DOES** · 01-12-2008, 01:11 AM

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** · 01-12-2008, 09:49 AM

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** · 01-13-2008, 02:22 PM

Originally Posted by Gos-C

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?