CODEWITHSUNDEEP
arraysexcelformula
luckyguy73
luckyguy73

Reputation: 1939

Use indirect to reference a cell to create formula

I want to take this array formula and use Indirect to reference a cell address where it would say either MAX, MIN, AVERAGE, or SUM.

{=MAX(IF(($A$2:$A$10=$F$1)*($B$2:$B$10=$F$2),$C$2:$C$10,""))}

If the cell address E3 contained MAX then I was trying to do this:

{=INDIRECT((E3)(IF(($A$2:$A$10=$F$1)*($B$2:$B$10=$F$2),$C$2:$C$10,"")))}

That formula causes a #REF! error for the Indirect(E3) portion. Once we can get it working we can copy the formula down and if Column E says MAX, MIN, SUM, or AVERAGE the correct indirect formula will run.

enter image description here

Thanks for your help.

Upvotes: 1

Views: 256

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

If you are open to a UDF then put this in a module attached to the workbook:

Function formulaConcat(ref As Range, form As String) As Variant
    formulaConcat = ActiveSheet.Evaluate(ref.Value & form)
End Function

Then you would need to enter the formula like:

=formulaConcat(E3,"(IF(($A$2:$A$10=$F$1)*($B$2:$B$10=$F$2),$C$2:$C$10))")

enter image description here

Upvotes: 2

Related Questions