Forum Discussion

SoyAllenChiu's avatar
SoyAllenChiu
Copper Contributor
Oct 01, 2023

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.

 

 

  • SoyAllenChiu 

    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))

Resources