Excel Data Validation

I have been trying to set up Excel cell data validation with drop down lists from VBA Behind an Access application. I can do it with reference to a simple (named or otherwise) range defining a list, but not with a validation formula containing functions like OFFSET.

I have also tried copying the cell validation, but it only seems possible in the same workbook.

Anyone have any suggestions on how I can set up a drop down lists in cells from Access VBA?