Autocomplete / Data Validation / Offset

[Abvlecxe]

Hi, I am looking to use VBA so that I can use autocomplete when using data validation lists, I came across this solution: http://www.contextures.com/xlDataVal10.html which works perfectly for normal validation lists.

However when I change my list from a standard list to using an offset formula the autocomplete doesn't work, does anyone know why and have a solution?

Thanks in advance