<div>
Several ways<br />
<br />
1) A small VBA can do this eliminating blanks to fill the dropdown<br />
2) A VBA to sort the data and only capture data except blanks<br />
<br />
Your choice<br />
gowflow
</div>


<div>
You also may want to look at this link<br />
<br />
<a href="http://www.cpearson.com/Excel/noblanks.htm" rel="ugc">http://www.cpearson.com/Excel/noblanks.htm</a><br />
<br />
gowflow
</div>


<div>
My knowledge with VBA scripting is limited ... any samples or leads that can assist?
</div>


<div>
Sure no sweat just post your sample workbook so this way the solution will be adapted exactly to your need.<br />
gowflow
</div>


<div>
Thank you. See the second dropdown list on the &quot;IndividualGraph&quot; sheet.<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882833/Graphs.xlsx" target="_blank" class="file-inline" title="dropdown (175 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Graphs.xlsx</a>
</div>


Graphs.xlsx

<div>
ok here it is I created in sheet in Col BA7 and down the following formula as Array (CTRL,SHIFT,ENTER) and I called this new Range NoBlankCell<br />

<pre><code class="code-1 nolinks" id="code-20-40439964-1"><span>=IFERROR(INDEX($A$7:$A$108,SMALL((IF(LEN($A$7:$A$108),ROW(INDIRECT(&quot;1:&quot;&amp;ROWS($A$7:$A$108))))),ROW(A1)),1),&quot;&quot;)</span>
</code></pre>
<br />
Then I modified your Range Name to be as follows:<br />

<pre><code class="code-1 nolinks" id="code-20-40439964-2"><span>=OFFSET(NoBlankCell,,,COUNTA(NoBlankCell)-COUNTBLANK(NoBlankCell))</span>
</code></pre>
<br />
Now check if this is what you want. I deliberately created stupid names as you can see in different rows to make sure it is working properly.<br />
<br />
NOTE<br />
I have to thank you for your question as I am not a 'Formula' Guy but rather a VBA one but seems the challenge and your extensive use of Named Ranges got me to fix it this way.<br />
<br />
Regards<br />
gowflow<br />
<a href="https://filedb.experts-exchange.com/incoming/2014/11_w46/882853/Graphs.xlsx" target="_blank" class="file-inline" title="Rem Blank lines (187 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Graphs.xlsx</a>
</div>


<div>
Thanks gowflow for your solution. The dropdown works as I wanted. I just notice that the class average no longer plots on the graph. I get a #VALUE!<br />
Must be something to do with the referencing. I'm just wondering would a VBA solution avoid that from happening?
</div>


<div>
Wait Wait.<br />
<br />
What is the Class average ? In what sheet ??? If in IndividualGraph the file you posted did not have the Average !!! In Summary the Average is there.<br />
<br />
Pllsadvise<br />
gowflow
</div>


<div>
Below the graph on the &quot;IndividualGraph&quot; sheet there's a table that displays the data for the selection from the dropdown lists. If you choose class average from the second dropdown the table no longer populates.
</div>


<div>
I see what you mean let me look at it and will revert.<br />
gowflow
</div>


Graphs-V01.xlsx

<div>
Perfect. Thank you for all your help
</div>


<div>
Your welcome, a small comment I did not understand why the first combo does not populate all the names !<br />
gowflow
</div>


<div>
The first dropdown excludes the Class Average from the name list and the second dropdown includes the Class Average from the name list. It was just a quick workaround to avoid Class Average being compared to Class Average on the graph
</div>


<div>
Ah Ic.<br />
If you need more help pls do not hesitate to put a link to any question you may have in here.<br />
<br />
Regards<br />
gowflow
</div>


<div>
Thank you. Much appreciated
</div>


<div>
<div class="content wysiwyg-content">
I'm using a dropdown list from the form control and populating it with a named range which gets its data from <br />
<br />
=MarkEntry!$A$7:$A$108<br />
<br />
The range accommodates about a 100 students, sometimes there are less, but the last row is always needed in the dropdown, because it has the class average. How would I ignore the blank lines when populating the dropdown.
</div>
</div>


I'm using a dropdown list from the form control and populating it with a named range which gets its data from 

=MarkEntry!$A$7:$A$108

The range accommodates about a 100 students, sometimes there are less, but the last row is always needed in the dropdown, because it has the class average. How would I ignore the blank lines when populating the dropdown.

Ignore blanks in Dropdown list from form control

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.