Dynamic Named Range on Visible cells

Column A is Dept ID. Column B is Program Name.

On anohter sheet I have two cells with data validation from the DeptID and Program Named lists. Using the data validation forDept ID triggers an autofilter which then filters the DeptID by the choice. I have a second data validation cell which is populated by the list of Program Names. I would like ONLY the Program Names that appear in the visible cells to populate this box, so need a Dynamic Named range of only the visible cells. On the internet I keep seeing that this is impossible, but I know the amazing minds at Excel Forum do the impossible before breakfast, so I'm confident of a solution.

Any help is greatly appreciated.

Thanks,
John

Presumably your autofilter is triggered by code, in which case you can copy the visible cells from col B to another location that you use as the DV source.

Nope. A DV list range has to be one contiguous row or column.