I am not sure what you are trying to do with the Data > Validation part of you post, but the OFFSET function is quite interesting - never used the Worksheet OFFSET before – then again the whole point is to lean something!! .
Apart for my machine not liking the Sheet1!$A$, part (Sheet1!$A$2, is OK! - assuming the top row are headings?). Using the range name Test as the Input Range of the Drop List it does indeed keep track of both inserted rows AND appending data to the bottom of the column in the list box, just great.
However the related Index functions I posted do not.
Therefore I suggest these also use OFFSET as follows
This will also keep tabs on additions to the bottom of the list.