trigger data validation warning

[Dave F]

Can the Data Validation tool fire if the cell has a formula rather than a
constant?

B24 has the formula =INDEX(C3:M13,MATCH(B18,B3:B13,0),MATCH(B21,C2:M2,0))
which can return a #N/A error if the user inputs values in B18 and B21 that
are not in B3:B13 and C2:M2, respectively. So I was hoping that if Data
Validation was set to require a whole number, I could get a warning text box
to pop up if #N/A is returned. Apparently, XL doesn't like that.

Is there a VBA-based solution to this?

Dave

 

[Elkar]

Data Validation only applies to manually input data. It won't be triggered
by a formula.

But, instead of applying validation to cell B24, why not place it in B18 and
B21 where the data is manually input?

Use a Custom Validation formula like:

=MATCH(B18,B3:B13,0)

HTH,
Elkar