Dynamic List From Single Column of Named Range Table (Permit Open/Close Bracket)

I'm stumped. I'm trying to fix a dynamic drop down list that was using an "=Offset" function in Excel, but is now getting #REF errors because I have created a macro to update the list from another document. This macro removes the old data (which creates the #REF) and refreshes it. I'm trying to figure out now how to refresh the list after the table has been updated.

I've managed to get past the steps where VBA is pulling the data, then creating the table, creating a named range, and finally creating a data validation list. My problem is in trying to specify a single column in the table, as I cannot get the brackets to be accepted by VBA so that my target for the list is "Group_ID_Table[Sub-Group Name]". I've finally managed to get the named range to create a function to provide "=Group_ID_Table" as the named range, as it was previously providing a text string of ""=Group_ID_Table"" (notice the extra double quote).

The problem I run into now is that the table is multiple columns, but data validation list can only accept a single column. When I try to specify the column "[Sub-Group Name]", I end up with a Run-time error '1004'. I believe that the open and close brackets are as I've tried substituting those characters with their ASCII values and concatenating the string together to create the appropriate named range "Group_ID_Table[Sub-Group Name]", but still run into an error.

Any suggestions?

Please Login or Register  to view this content.

Changed my target for the Named Range to

Please Login or Register  to view this content.

and it worked.