INDIRECT and named ranges

I would pivot that column mate then create your dynamic named range based on the data in that pivot.

i'd also add a refresh all pivots in the workbook_open module and on the worksheet_activate module that way your book will always be up to date.

hope that helps

Private Sub Workbook_Open()
ActiveWorkbook.RefreshAll
End Sub

Private Sub Worksheet_Activate()
ActiveWorkbook.RefreshAll
End Sub

HTH