Update multiple Named Ranges created by Dynamic Source using Spill

In Excel 365, currently I manually update multiple named ranges based on a dynamic header row, with dynamic values under each header. I hoping someone can help me with using VBA code to update the names and ranges all together based on the current data/table.

The goal is to have a drop down list with the relevant Units values, without blanks or duplicates, for each Item.

The source data comes from an external workbook that is updated weekly. The data is in columns of Item and Units, but there is often multiple instances of each Item and Units and each week there may be new Items or some removed, same with the associated Units values. The source is as per below example:

The unique headers are pulled using the formula

=IFERROR(INDEX($B$4:$B$600, MATCH(0, COUNTIF($K$2:K2, $B$4:$B$600), 0)),"")

The following formula is in the row below the headers, to pull the unique values for each header

=UNIQUE(FILTER($C$4:$C$600,$B$4:$B$600=L2))

The resulting table is as per below example:

Currently I have highlight all the headers and the row below and select Create from Selection under Defined Names and select Top row. This creates named ranges that refer to the cell below each header. I then edit each range that the name refers to, by adding # at the end, so it refers to the spilled data, as it is a dynamic range.

e.g. Update the named range reference for AA_120 from =SHEET1!$L$3 to =SHEET1!$L$3#

I do this one by one for 100+ named ranges. Any tips or help to make this more efficient?

oh and the formula I'm using for the source of the Data Validation is =INDIRECT(C7) where C7 is the Item/named range. This all works well...

TIA

EDIT: I worked out some VBA code to add the # at the end of the referred range. The range for all Named Ranges starts on row 3, so this worked to update all relevant ranges at once...

Sub RangeRename()

Dim n As Name
    For Each n In Names
        If Right(n.RefersTo, 1) = "3" Then n.RefersTo = n.RefersTo & "#"
    Next n
End Sub

Would still really appreciate it if someone could improve on my VBA, to update all the ranges from when the source data is updated. Currently I delete all named ranges in the Name Manager, then select the 2 rows with all the updated Item and Units data. Then Create from Selection under Defined Names and select Top row. Then I run my macro. But if the Macro could do all above, that would be great. The difficulty I see if that the amount of Items and corresponding Units vary as it is dynamic. Plus there are 3 data sources on 3 different sheets, all rows 2&3 but columns start at L, N & T for the 3 sheets.

What I have worked out will work for me, but if anyone can improve on my code, I'd be extremely grateful!

Just in case anyone is wanting to achieve the same thing.

I used a filter to create the column of items, then TRANSPOSE(UNIQUE(FILTER(G:G,B:B=T7 for the rows of unit options next to each item.

For the VBA I added the following to a module

Function xDAV(c As Range) As Range
Dim r As Range
 
With Sheets("LookUpList").Columns("T:T") 'Items
    
    Set r = .Find(What:=c.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
    'Units are in col U
    'col U is 1 column to the right from col T
    n = 1 'change to suit
    
    If Not r Is Nothing Then
        Set f = r.Offset(, n)
            If f.Offset(, 1) = "" Then
                Set xDAV = f 'single cell or blank cell
            Else
                rc = f.End(xlToRight).Column 'get last column with data
                Set xDAV = f.Resize(, rc + 1 - f.Column)
            End If
    End If

End With

End Function

Then in the data validation I entered =toXDAV in the source field.