I creating an Excel Spreadsheet with multiple pages for work to aid in dispatching drivers. Sheet 1 is the main age with all the good stuff, while Sheet 2 contains a very long list of customer names and addresses. Sheet one uses a data validation list to select customers from the list contained on sheet 2. The drop down that the validation list offers make for easy selection from the list, but since the list is so long I would like to take it to the next level by incorporating an autocomplete feature on the drop down. I know that this is not possible in Excel unless the master list is directly above the cell with the validation list. Due to other factors I cann't do this, the master list has to be on a seperate sheet. I've discovered that by using some VBA programming I can fool Excel into having an autocomplete, like what is here http://www.contextures.com/xlDataVal10.html . This works great but the list still has to be on the same sheet.
My question is how can I get autocomplete features on a data validation list where the master list is on a different sheet?