Help with formula

[H1D3F]

Hello,

I am trying to remove the blank selections from a dependent drop list I have created in Excel and I am having a hard time figuring out what the formula would be to get this accomplished. A little background, I have a table with two columns in the first column I have a drop down list feeding off of another sheet to get the manager names, formula is =Sheet1!$A$1:$EO$1. Then in second column I have a dependent drop down list that will populate specific portfolios based off the manager I had selected in the first column, formula is =INDEX(Sheet1!$A$2:$EO$67,,MATCH($A$5,Sheet1!$A$1:$EO$1,0)). The problem I am having is that most of the managers only have about 5 portfolios but one of those has about 65 portfolios, so when I select one of the smaller ones I have a bunch of blank spaces in my drop down list. Any help on how to get this resolved would be welcomed.

[#2]

Hello @H1D3F,

if the different managers have varying numbers of portfolios, then you need a distinct range for each manager that includes only the rows of data for that manager.  In the attached spreadsheet you find three managers with three different range names. They have been created by selecting the manager name and the cells with text below it, then using Insert > Name > Create with the default to use the text in the top row for the range name. Any space in the range name will be replaced with an underscore. 

The range name for portfolio uses the formula 

=INDIRECT(SUBSTITUTE(Sheet1!$A3," ","_"))

It replaces the space in column A with an underscore and then uses that string to refer to the range name.

[#3]

@H1D3F 

hello,

Please check the attached file. you can find the explanation of the formula in below wep page, in the second tab. It is in Turkish however you can use web translator. (BAĞ_DEĞ_DOLU_SAY is COUNTA).

http://excelileharikalar.com/index.php/2018/09/06/birbirine-bagli-listeledr/

best regards,

[#4]

@H1D3F if anyone was curious, this is the formula I used in data validation to get all the blank spaces in my drop down list gone except for one. =OFFSET(Sheet1!$A$2:$EO$63,0,MATCH($A$5,Sheet1!$A$1:$EO$1,0)-1,COUNTA(OFFSET(Sheet1!$A$1,0,MATCH($A$5,Sheet1!$A$1:$EO$1,0)-1,65,1)),1)

[#5]

@H1D3F 

if you used the formula I have provided in sample file please mark the answer as best solutuion...

[#6]

@H1D3F 

In the attached file, the formula for CountedPortfolios is: 

=INDEX(Portfolios,1,

MATCH(!A2,Managers,0)):INDEX(Portfolios,

PortfolioCount,MATCH(!A2,Managers,0))

Note that all formulas are dynamic.