Good morning all. As usual, I need help.

I would like to have a dynamic drop down list based on criteria. Not sure how to do it with a dynamic named range. I am using the following from another thread which works great, but I would like it to be filtered based on criteria.

The STUDENT sheet will have a list of names with a column that has a STATUS (Present, Absent, Late). I wish to have the list display only the names that are not marked Absent.


http://www.excelforum.com/showthread.php?t=609580
Quote Originally Posted by Jazzer
Hi,

You can do it by using dynamic named range. First define a new named range:

=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),1)
and give it a name, let's say "list"

The above formula works fine if your list starts from Sheet1!A1 and has no blank cells in it (and nothing else in Sheet1!A:A under the list itself).

Secondly use the named range "list" in your drop down box. You can use it in Data Validation -> List or in Form Control -> Combo Box.

To use it in Data Validation, select a cell (or cells) you want to appy it to. Select Data -> Validation, change Data Validation criteria to List and in Source type "=list" and press OK.

In Form Cotrols Combo Box, first insert a Combo Box. The right click it and choose Format Cotrol. In Cotrol tab, type "list" in Input Range and press OK.

Hope this helps.

- Asser
Any help/guidance will be much appreciated.

Tim E.