how to do data validation across workbooks

**dmcgov** · 08-07-2017, 11:59 AM

i am trying to make this work based on this link:

but when i put in the info, excel (2016) says it can't be done.

Can i do data validation across multiple workbooks?

**dmcgov** · 08-07-2017, 12:44 PM

never mind, i figured it out. but still i have a vendor excel file with names, vendor#, address1-2-3,address phone, etc. is there a way to pull all or some of that data across at the same time?

**FDibbins** · 08-07-2017, 01:29 PM

upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

**dmcgov** · 08-08-2017, 10:52 AM

sorry FDibbins for taking so long to do this. but here is the workbook.

here is information that you will need for this.

The formulas page holds the table for the vendor list. I would like that in a separate file so that i can refer to only one vendor excel file (i have multiple uses for this, not just purchase orders).

When you go to the "Purchase Order (1)" sheet, you can see (on the upper left) a section for vendor information, like name, address and phone.

what i have that works somewhat: put a data validation in cell C5 and then use VLOOKUPs for the remaining four lines. Issue is if I put the vendor (and source the newly created vendor worksheet), then the vendor worksheet needs to be open for the dropdown to display. which means some kind of mechanism to automatically open the file (whether its readonly or readwrite, most likely readonly).

is there a way to use VBA to create this same type of functionality but not make it so that I have to open the vendor file?