Indirect (Substitute) - limit on references?

G

GalleyOC

New Member
Joined
Feb 19, 2016
Messages
13
Hello all.
I am using the formula =INDIRECT(SUBSTITUTE($B$10, " ", "_")) to pull a list of advisors based on the manager name that appears in a dropdown cell(B10). The list is on a separate sheet in the same workbook.
The manager names are listed horizontally and the advisors under them are listed in each column under the managers name.

I have one column that contains all of the names but when I select All(the name of the column) it only pulls 23 names instead of all of them. Is there a limit to this formula that only pulls a certain number of results? Or does anyone have a better way to pull this result?

This is the results page where I have the formula B10
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 127px"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCCCCC]#CCCCCC[/URL] , align: right"]Program Level[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]STDEV[/TD]
[/TR]
[TR]
[TD]MEAN[/TD]
[/TR]
[TR]
[TD="align: right"]UCL[/TD]
[/TR]
[TR]
[TD="align: right"]Targets[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] , align: right"]Team Based[/TD]
[/TR]
[TR]
[TD="align: right"]Team Leader[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]ALL[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 1[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 3[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 5[/TD]
[/TR]
[TR]
[TD]Name 6[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 7[/TD]
[/TR]
[TR]
[TD]Name 8[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 9[/TD]
[/TR]
[TR]
[TD]Name 10[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 11[/TD]
[/TR]
[TR]
[TD]Name 12[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 13[/TD]
[/TR]
[TR]
[TD]Name 14[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 15[/TD]
[/TR]
[TR]
[TD]Name 16[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 17[/TD]
[/TR]
[TR]
[TD]Name 18[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 19[/TD]
[/TR]
[TR]
[TD]Name 20[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 21[/TD]
[/TR]
[TR]
[TD]Name 22[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "]Name 23[/TD]
[/TR]
[TR]
[TD]Name 24[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CCFFCC]#CCFFCC[/URL] "][/TD]
[/TR]
</tbody>[/TABLE]



This is the Alignment page containing the names
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 190px"><col width="140"><col width="171"><col width="193"><col width="149"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=38761D]#38761D[/URL] "]ALL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=38761D]#38761D[/URL] , align: center"]Manager 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=38761D]#38761D[/URL] , align: center"]Manager 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=38761D]#38761D[/URL] , align: center"]Manager 3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=38761D]#38761D[/URL] , align: center"]Manager 4[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6D7A8]#B6D7A8[/URL] "]Director[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6D7A8]#B6D7A8[/URL] , align: center"]Mobile Support[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6D7A8]#B6D7A8[/URL] , align: center"]Onboarding[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6D7A8]#B6D7A8[/URL] , align: center"]Mobile Support[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B6D7A8]#B6D7A8[/URL] , align: center"]Mobile Support[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f3f3f3]#f3f3f3[/URL] "]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f3f3f3]#f3f3f3[/URL] , align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f3f3f3]#f3f3f3[/URL] , align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f3f3f3]#f3f3f3[/URL] , align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=f3f3f3]#f3f3f3[/URL] , align: center"]13[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]Name 2[/TD]
[TD]Name 3[/TD]
[TD]Name 4[/TD]
[TD]Name 5[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Name 3[/TD]
[TD]Name 4[/TD]
[TD]Name 5[/TD]
[TD]Name 6[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]Name 4[/TD]
[TD]Name 5[/TD]
[TD]Name 6[/TD]
[TD]Name 7[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]Name 5[/TD]
[TD]Name 6[/TD]
[TD]Name 7[/TD]
[TD]Name 8[/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD]Name 6[/TD]
[TD]Name 7[/TD]
[TD]Name 8[/TD]
[TD]Name 9[/TD]
[/TR]
[TR]
[TD]Name 6[/TD]
[TD]Name 7[/TD]
[TD]Name 8[/TD]
[TD]Name 9[/TD]
[TD]Name 10[/TD]
[/TR]
[TR]
[TD]Name 7[/TD]
[TD]Name 8[/TD]
[TD]Name 9[/TD]
[TD]Name 10[/TD]
[TD]Name 11[/TD]
[/TR]
[TR]
[TD]Name 8[/TD]
[TD]Name 9[/TD]
[TD]Name 10[/TD]
[TD]Name 11[/TD]
[TD]Name 12[/TD]
[/TR]
[TR]
[TD]Name 9[/TD]
[TD]Name 10[/TD]
[TD]Name 11[/TD]
[TD]Name 12[/TD]
[TD]Name 13[/TD]
[/TR]
[TR]
[TD]Name 10[/TD]
[TD]Name 11[/TD]
[TD]Name 12[/TD]
[TD]Name 13[/TD]
[TD]Name 14[/TD]
[/TR]
[TR]
[TD]Name 11[/TD]
[TD]Name 12[/TD]
[TD]Name 13[/TD]
[TD]Name 14[/TD]
[TD]Name 15[/TD]
[/TR]
[TR]
[TD]Name 12[/TD]
[TD]Name 13[/TD]
[TD]Name 14[/TD]
[TD]Name 15[/TD]
[TD]Name 16[/TD]
[/TR]
[TR]
[TD]Name 13[/TD]
[TD]Name 14[/TD]
[TD]Name 15[/TD]
[TD]Name 16[/TD]
[TD]Name 17[/TD]
[/TR]
[TR]
[TD]Name 14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 22[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 32[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Maybe...

=IFERROR(INDEX(INDIRECT(SUBSTITUTE($B$10," ","_")),ROWS(A$1:A1)),"")

M.
 
Upvote 0
Thanks Marcelo, I tried this but unfortunately it still results in the same amount of values being displayed.
 
Upvote 0
GalleyOC said:
Thanks Marcelo, I tried this but unfortunately it still results in the same amount of values being displayed.
Click to expand...

Check how many rows the named range All has.
Try this formula in an empty cell
=ROWS(All)
tell us the result

M.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Problem with XLOOKUP on array
Replies
3
Views
190
Fluff
Fluff
S
  • Question Question
Macro to change one cell on a sheet, save sheet as PDF, then loop back and change that one cell on that sheet and repeat until the end
Replies
3
Views
641
IslandofBDA
I
Sufiyan97
  • Solved
VBA Filter Names from List One by One and then Copy filtered data v2
Replies
6
Views
176
Sufiyan97
Sufiyan97
D
  • Question Question
Using UNIQUE & FILTER with SPILL ranges
Replies
4
Views
570
kevin9999
K
E
  • Question Question
Generate unique code based on user form entry and put in table
2
Replies
18
Views
483
Micron
M

Forum statistics

Threads
1,223,713
Messages
6,174,043
Members
452,542
Latest member
Bricklin

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