Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Formula
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). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Mac" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|