Custom Data Validation Help

[jgeorg0310]

Hi,

I am trying to create a custom data validation and I need some help with the formula syntax. In my spreadsheet Column B has a drop down that says "Refund" and "Charge". I want to create the custom data validation in Column D to limit the number of characters in the cell so that any line that has "Refund" selected is limited to only 4 numbers and any line with "Charge" selected is limited to 15 or 16 numbers. Any assistance would be much appreciated! Thanks!

 

[DushiPunda]

I only tested this with B2/B3 & D2/D3, but it should do the job:

=IF($B2="Refund",LEN($D2)=4,IF($B2="Charge",AND(LEN($D2)>14,LEN($D2)<17),""))

$B2 & $D2 will need to be changed to the first row you are applying the data validation to. So, if the first row is B11/D11, then the formula applied to the entire range would be:

=IF($B11="Refund",LEN($D11)=4,IF($B11="Charge",AND(LEN($D11)>14,LEN($D11)<17),""))