Data Validation quaestion

  • Thread starter Thread starter Andrew Mackenzie
  • Start date Start date
A

Andrew Mackenzie

Hi all,

Any help with this one much appreciated.

I want to validate the data in cell B1 so that entry is prevented if the
date in Cell A1 does not appear in a named range of dates in a different
sheet in the same workbook. I assume that something with =MATCH will do the
job but can't figure it out - can you?

TIA,

Andrew
 
Assume your date range is named "date".

Select B1, then,
<Data> <Validation>
Under "Allow" choose "Custom",
Then, in the formula box enter:

ISNUMBER(MATCH(A1,date,0))

Then, click on "Error Alert",
and type something like this:

"Date in Column A is incorrect"

So that users will realize that the entry in Column B is not the problem.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi all,

Any help with this one much appreciated.

I want to validate the data in cell B1 so that entry is prevented if the
date in Cell A1 does not appear in a named range of dates in a different
sheet in the same workbook. I assume that something with =MATCH will do the
job but can't figure it out - can you?

TIA,

Andrew
 
Don't forget the = sign:

=ISNUMBER(MATCH(A1,date,0))

like I did<bg>
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------



Assume your date range is named "date".

Select B1, then,
<Data> <Validation>
Under "Allow" choose "Custom",
Then, in the formula box enter:

ISNUMBER(MATCH(A1,date,0))

Then, click on "Error Alert",
and type something like this:

"Date in Column A is incorrect"

So that users will realize that the entry in Column B is not the problem.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Hi all,

Any help with this one much appreciated.

I want to validate the data in cell B1 so that entry is prevented if the
date in Cell A1 does not appear in a named range of dates in a different
sheet in the same workbook. I assume that something with =MATCH will do the
job but can't figure it out - can you?

TIA,

Andrew
 
Back
Top