Need Formula

[Mac]

Hi. Using Excel 2007. I want to be able to force information into a cell - in
order to protect or "bullet-proof" the data-capture in order to limit capture
errors. For example: If Column B contains the text: "SAI", then Column C must
default to a 13-digit numeric field. If any alphanumeric or less than 13
digits is captured in Column C, it must not allow this and force an error
message. However, if Column B contains the text: "Passport", then column C
must default to an alphanumeric field of unlimited length (because passport
no's differ in length and some are numeric as well as alphanumeric).

is this possible within Excel's normal functions? Please can you help.

Thanks

 

[Luke M]

Something like this setup as custom data validation rule for cell C2:

=IF(B2="SA1",AND(LEN(C2)=13,ISNUMBER(C2)),TRUE)

However, do note that a sneaky user could change B2 to something other than
SA1, input their value into C2, and then go back and change B2.

To stop this, the data validation rule for B2 should be:
=ISBLANK(C2)

This forces the user to clear out C2 before changing B2 (and then the rule
in C2 will check their input).