Data Verification in Excel from list in Access

[farside]

I have excel templates that need a drop down box in one column so that users can choose from a list of 'products' in an Access database. Using this method as doing it from an Excel Workbook means you have to have the Workbook with the product list open ALL the time. Anyone know how I can do it using Access?

 

[Ruddles]

If the list stays static throughout the day, import the Access table into a hidden worksheet when you open the workbook (in the Workbook_Open event handler) and name the range, then point the data validation at that range.

 

[farside]

Thanks but will this work if the scenario is as follows:

Mix of excel templates that use this 'product' list
So, if we do as you suggest and we then update the product list in Access, will all the templates update?

Thanks

 

[Ruddles]

If you place the code which imports the tables from Access in the Workbook_Open event, they will update every time they open.

 

[farside]

Thank you very much