Dynamic Names in Formula

B

BriCris

New Member
Joined
May 30, 2017
Messages
11
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:
HTML: 
   =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]
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Sorry, I don't understand your setup. Are you saying that the "helper page" contains the complete list of managers, and that the table will not necessarily include all the managers?

I also don't understand what your formula is trying to do. What results are you trying to determine, and can you give us some examples?
 
Upvote 0
My Helper page has a list of manager names that change from month to month. I get a new Table every month and sometimes the names and number of managers change. 1 month I could have 3 Managers and the other month I could have 4 managers.
[TABLE="class: cms_table_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]

The Formula is pulling info from the table basted on the Manager Names:
=SUMPRODUCT(--(INDIRECT(H$3 & "[Manager Name]")="Manager One)--(INDIRECT(H$3 & "[Manager Name]")="Manager Two")--(INDIRECT(H$3 & "[Manager Name]")="Manager Three")
=SUMPRODUCT(Apr_2017[Manager Name]="Manager One",Apr_2017[Manager Name]="Manager Two",Apr_2017[Manager Name]="Manager Three")

I want to first count Managers names then I want to get a count from the table that will give me a count of customers that belong to a manager that have a LTR Score of<=7.
Manager Apr_2019[LTR Score]")<=7.

I hope this helps.

Brian
 
Upvote 0
Example:
=SUMPRODUCT(--(INDIRECT(C3&"[Manager Name]")="Manager One")--(INDIRECT(C3&"[Manager Name]")="Manager Two")--(INDIRECT(C3&"[Manager Name]")="Manager Three"))

New month, new data has 1 more manager in it. Now I need to changed 100 + formulas.

New formula to add new manager.
=SUMPRODUCT(--(INDIRECT(C3&"[Manager Name]")="Manager One")--(INDIRECT(C3&"[Manager Name]")="Manager Two")--(INDIRECT(C3&"[Manager Name]")="Manager Three")--(INDIRECT(C3&"[Manager Name]")="Manager Four"))


I'm looking for a we to use a formula that will pull the names of the managers from the helper pager no mater if there is 3 or 5 managers in it.

I have tried using =INDIRECT("'" & G16 & "'!" & G18) to get the name of the Tab and the cell but it will not work with blank fields. If I do 4 sumproduct will not work I get a error.

=INDIRECT("'" & G16 & "'!" & G18) =Helper!G18 = Manager One

=INDIRECT("'" & G16 & "'!" & G18) =Helper!G18 = Manager Two

=INDIRECT("'" & G16 & "'!" & G18) =Helper!G18 = Manager Three

=INDIRECT("'" & G16 & "'!" & G18) =Helper!G18 = Blank Field.

Brian
 
Upvote 0
I would create a unique table adding a new column, Month, and ...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Month​
[/td][td]
Manager Name​
[/td][td]
LTR Remarks​
[/td][td]
LTR Score​
[/td][td][/td][td]
Month​
[/td][td]
Count of Managers​
[/td][td]
LTR <=7​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
June​
[/td][td]
Manager One​
[/td][td]
Very Helpful.​
[/td][td]
7​
[/td][td][/td][td]
June​
[/td][td]
4​
[/td][td]
3​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
June​
[/td][td]
Manager Two​
[/td][td]
Very Friendly.​
[/td][td]
8​
[/td][td][/td][td]
July​
[/td][td]
5​
[/td][td]
4​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
June​
[/td][td]
Manager Three​
[/td][td]
Excellent Service.​
[/td][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
June​
[/td][td]
Manager Four.​
[/td][td]
Not Happy.​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
July​
[/td][td]
Manager One​
[/td][td]
Very Helpful.​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
July​
[/td][td]
Manager Two​
[/td][td]
Very Friendly.​
[/td][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
July​
[/td][td]
Manager Three​
[/td][td]
Excellent Service.​
[/td][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
July​
[/td][td]
Manager Four.​
[/td][td]
Not Happy.​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
July​
[/td][td]
Manager Five​
[/td][td]
Very Helpful.​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in G2 copied down
=COUNTIFS(Table1[Month],F2,Table1[Manager Name],"<>")

Formula in H2 copied down
=COUNTIFS(Table1[Month],F2,Table1[LTR Score],"<=7")

Hope this helps

M.
 
Upvote 0
Marcelo Branco said:
I would create a unique table adding a new column, Month, and ...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Month​
[/TD]
[TD]
Manager Name​
[/TD]
[TD]
LTR Remarks​
[/TD]
[TD]
LTR Score​
[/TD]
[TD][/TD]
[TD]
Month​
[/TD]
[TD]
Count of Managers​
[/TD]
[TD]
LTR <=7​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
June​
[/TD]
[TD]
Manager One​
[/TD]
[TD]
Very Helpful.​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD]
June​
[/TD]
[TD]
4​
[/TD]
[TD]
3​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
June​
[/TD]
[TD]
Manager Two​
[/TD]
[TD]
Very Friendly.​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD]
July​
[/TD]
[TD]
5​
[/TD]
[TD]
4​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
June​
[/TD]
[TD]
Manager Three​
[/TD]
[TD]
Excellent Service.​
[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
June​
[/TD]
[TD]
Manager Four.​
[/TD]
[TD]
Not Happy.​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
July​
[/TD]
[TD]
Manager One​
[/TD]
[TD]
Very Helpful.​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
July​
[/TD]
[TD]
Manager Two​
[/TD]
[TD]
Very Friendly.​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
July​
[/TD]
[TD]
Manager Three​
[/TD]
[TD]
Excellent Service.​
[/TD]
[TD]
6​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
July​
[/TD]
[TD]
Manager Four.​
[/TD]
[TD]
Not Happy.​
[/TD]
[TD]
2​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
July​
[/TD]
[TD]
Manager Five​
[/TD]
[TD]
Very Helpful.​
[/TD]
[TD]
7​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in G2 copied down
=COUNTIFS(Table1[Month],F2,Table1[Manager Name],"<>")

Formula in H2 copied down
=COUNTIFS(Table1[Month],F2,Table1[LTR Score],"<=7")

Hope this helps
----------------------
M.[/QUOTE

Thanks for your Help but I get a new set of Data every month that I put in a table. All the fields are the same and the rows are over 5K to 6k each month.

I have everything setup so that all anyone needs to do it is add the new month except this.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
=INDEX(INDIRECT is shifting one column over
Replies
3
Views
283
Tomeegee
T
K
  • Locked
  • Question Question
excel vba color tab + name manager
Replies
1
Views
545
Fluff
Fluff
M
  • Question Question
Play around with email address - get the name and company
Replies
3
Views
695
Kerryx
K
alm395
  • Question Question
Filter Report based on Dates
Replies
1
Views
775
fadee2
fadee2
E
  • Solved
VBA Self populate page name in formula
2 3
Replies
22
Views
2K
johnnyL
J

Forum statistics

Threads
1,223,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top