Changing Names in Formula

[BriCris]

I'm looking for help setting up a formula that will change with manager names from the helper page. The formula need to work when there is More or less managers ie. some months we will have 4 managers and others we will only have 2 managers.

I have tried using =INDIRECT("'" & G16 & "'!" & G18) that will pull the tab Helper!D2 to get names and that works but I don't know how to make the formula that will work with more or less managers so that I don't have to change 100s of formulas every month.

Tables/Tabs

Table
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Manager Name
[/TD]
[TD="align: center"]LTR Remarks[/TD]
[TD="align: center"]LTR Score[/TD]
[/TR]
[TR]
[TD="align: center"]Manager One[/TD]
[TD="align: center"]Very Helpful.
[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]Manager Two[/TD]
[TD="align: center"]Very Friendly.
[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]Manager Three[/TD]
[TD="align: center"]Excellent Service.[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]Manager Four.[/TD]
[TD="align: center"]Not Happy.[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]

Formula:

   =SUMPRODUCT(--(INDIRECT(H$3   &"[Manager Name]")="Manager One)--(INDIRECT(H$3 &"[Manager Name]")="Manager Two")--(INDIRECT(H$3 &"[Manager Name]")="Manager Three"),--(INDIRECT(H$3 & "[LTR   Score]")<=7),--(LEN(INDIRECT(H$3 & "[LTR   Remarks]"))-LEN(SUBSTITUTE((UPPER(INDIRECT(H$3 & "[LTR   Remarks]"))),UPPER($A22),"")))/LEN($A22))

Tab with Formula:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Neg Remarks
[/TD]
[TD="align: center"]LTR
[/TD]
[TD="align: center"]Rep Sat
[/TD]
[TD="align: center"]Apr_2017 ($M$3)
[/TD]
[/TR]
[TR]
[TD="align: center"](Blank)
[/TD]
[TD="align: center"](Formula from above)
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Helpful (A22)
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Friendly
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Helper Page:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Manager Names
[/TD]
[/TR]
[TR]
[TD="align: center"]Manager One
[/TD]
[/TR]
[TR]
[TD="align: center"]Manager Two
[/TD]
[/TR]
[TR]
[TD="align: center"]Manager Three
[/TD]
[/TR]
[TR]
[TD="align: center"]Manager Four
[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Duplicate: Dynamic Names in Formula