Using a different formula depending on the settings of multiple variables in Excel

[mhoehener]

Hi all

Hope for help here

Situation:
I am trying to use a different formula in Cell A depending on multiple variables in Cell B, C (possible will grow in the future and more variables will be introduced - but as of right now, it is only 2. But it is the reason I do not want to solve this with a nested IF).

So far I was not able to solve this problem.

The following was my approach:

Create a UDF named FormulaValue

In the Function use a Select Case checking on the variable combination of cells B, C

Values for B and C can be either 'selection' or 'no selection' as it is 2 variable right now, there are 4 possible combinations.

[I]Function FormulaValue(Inputfield)

    Select Case Inputfield
    
        Case Is = "no selection|no selection"
        
            FormulaValue = "=SUMIFS(data!G:G,data!H:H," >= "&DATE($D$6,$E$6,$F$6),data!H:H," <= "&DATE($G$6,$H$6,$I$6),data!BT:BT,'Results'!C24)"
        
        Case Is = "selection|no selection"
        
            FormulaValue = "=SUMIFS(data!G:G,data!H:H," >= "&DATE($D$6,$E$6,$F$6),data!H:H," <= "&DATE($G$6,$H$6,$I$6),data!BT:BT,'Results'!C23,data!BR:BR,'Results'!$N$4)"
        
        Case Is = "no selection|selection"
        
            FormulaValue = "=SUMIFS(data!G:G,data!H:H," >= "&DATE($D$6,$E$6,$F$6),data!H:H," <= "&DATE($G$6,$H$6,$I$6),data!BT:BT,'Results'!C23,data!BY:BY,'Results'!$O$4)"
        
        Case Is = "selection|selection"
        
            FormulaValue = "=SUMIFS(data!G:G,data!H:H," >= "&DATE($D$6,$E$6,$F$6),data!H:H," <= "&DATE($G$6,$H$6,$I$6),data!BT:BT,'Results'!C23,data!BY:BY,'Results'!$O$4,data!BR:BR,'Results'!$N$4)"
            
    End Select
    
End Function

[/I]

The result when adding this UDF in excel is #Value. Clicking on the function to see what happens shows that Inputfield is one of the Select cases, but then no result is provided.

Thanks for your feedback.

Regards Marco

 

[Fluff]

Hi & welcome to MrExcel.
You cannot use a UDF to change the formula in a cell.

 

[mhoehener]

Hi & welcome to MrExcel.
You cannot use a UDF to change the formula in a cell.

Hi. Thanks for the quick feedback. so my only solution is an ever-extending nested if or is there a better way to solve my issue?

 

[Fluff]

You could possibly put the IF into the sumifs, but it's going to get messy either way.

 

[mhoehener]

T

You could possibly put the IF into the sumifs, but it's going to get messy either way.

Thanks again for the reply. I solved it no with the new IFS formula that checks on a code that I create depending on the combination of the different variables. Not perfect but better then a nested IF formula.

 

[Fluff]

better then a nested IF formula.

I wouldn't count on that. The new IFS function is far less efficient then nested IF functions.
Glad you sorted it & thanks for the feedback.

 

[mhoehener]

I wouldn't count on that. The new IFS function is far less efficient then nested IF functions.
Glad you sorted it & thanks for the feedback.

let's hope don't know about efficiency but imho certainly more readable