Indirect (Substitute) - limit on references?

[GalleyOC]

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>

Program Level

STDEV

MEAN

UCL

Targets

Team Based

Team Leader

ALL

Name 1

Name 2

Name 3

Name 4

Name 5

Name 6

Name 7

Name 8

Name 9

Name 10

Name 11

Name 12

Name 13

Name 14

Name 15

Name 16

Name 17

Name 18

Name 19

Name 20

Name 21

Name 22

Name 23

Name 24

<colgroup><col style="width: 127px"></colgroup><tbody>
</tbody>

This is the Alignment page containing the names
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

ALL	Manager 1	Manager 2	Manager 3	Manager 4
Director	Mobile Support	Onboarding	Mobile Support	Mobile Support
25	13	13	13	13
Name 1	Name 2	Name 3	Name 4	Name 5
Name 2	Name 3	Name 4	Name 5	Name 6
Name 3	Name 4	Name 5	Name 6	Name 7
Name 4	Name 5	Name 6	Name 7	Name 8
Name 5	Name 6	Name 7	Name 8	Name 9
Name 6	Name 7	Name 8	Name 9	Name 10
Name 7	Name 8	Name 9	Name 10	Name 11
Name 8	Name 9	Name 10	Name 11	Name 12
Name 9	Name 10	Name 11	Name 12	Name 13
Name 10	Name 11	Name 12	Name 13	Name 14
Name 11	Name 12	Name 13	Name 14	Name 15
Name 12	Name 13	Name 14	Name 15	Name 16
Name 13	Name 14	Name 15	Name 16	Name 17
Name 14
Name 15
Name 16
Name 17
Name 18
Name 19
Name 20
Name 21
Name 22
Name 23
Name 24
Name 25
Name 26
Name 27
Name 28
Name 29
Name 30
Name 31
Name 32
Name 33

<colgroup><col style="width: 190px"><col width="140"><col width="171"><col width="193"><col width="149"></colgroup><tbody>
</tbody>

 

[Marcelo Branco]

Maybe...

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

M.

 

[GalleyOC]

Thanks Marcelo, I tried this but unfortunately it still results in the same amount of values being displayed.

 

[Marcelo Branco]

Thanks Marcelo, I tried this but unfortunately it still results in the same amount of values being displayed.

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

M.

 

[GalleyOC]

Argh - So Simple.. I thought I updated the named range but I didn't!
Thank you again Marcelo!

 

[Marcelo Branco]

Argh - So Simple.. I thought I updated the named range but I didn't!
Thank you again Marcelo!

You are welcome. Glad to help.

M.