Forum Discussion
HELP WITH FORMULA
Hi, I want to create a formula where I prevent some multiple inputs in 1 single cell.
=(IF(ISNUMBER(SEARCH({"ACCOUNT LATE","ACCOUNT PENALTY","SAVING PENALTY","BUSINESS LATE","BUSINESS PENALTY","ENTERPRISE LATE","ENTERPRISE PENALTY","OTHER LATE"},B85)),"INPUT ERROR",(0)))
IN MY CASE THE FORMULA RETURNS #SPILL.
What I want to prevent is :
The user from combining these highlights : ACCOUNT, LATE, PENALTY, SAVING BUSINESS, ENTERPRISE, OTHER.
These highlights are key for telling excel when to use formulas. Now my formulas working awesome but when you combine them, it might sum all formulas or it might give more results in such as: if account supposed to result 500 and late 600, when combined, it always result in 1100.
As result I want excel to return "input error" when more that 1 highlight is provided in 1 cell.
Can u guys help me create that formula to prevent these errors of combining results.
Use
=(IF(OR(ISNUMBER(SEARCH({"ACCOUNT LATE","ACCOUNT PENALTY","SAVING PENALTY","BUSINESS LATE","BUSINESS PENALTY","ENTERPRISE LATE","ENTERPRISE PENALTY","OTHER LATE"},B85))),"INPUT ERROR",0))
- Thank you.
