Dynamic Drop Down List...with Criteria

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

Originally Posted by Jazzer

Hi,

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

Please Login or Register  to view this content.

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.

hi timE,

have a look at this attachment its aworking example of data validation, with both list that are static and dynamic with lists from other worksheets.

hope it helps reg