I have a spreadsheet template I use to track vital signs, test scores, medication doses, and lab tests for the physician I work for. At the top of the columns designated for tests, medications, and labs, I have a dropdown list created by using the Data Validation function. The list that each dropdown pulls from resides on a separate spreadsheet called "MasterList.xlsx." The lists are in named ranges, so I can edit the lists more easily (which I have to do a lot).

I previously had the lists on a separate sheet in the same template, but this was problematic. I could add a new medication or lab test to the list in the template, but previous spreadsheets created from that template would not have the new information, requiring it be added manually and piecemeal. Using MasterList propagates the changes through every spreadsheet based on the template.

The current system still has problems.

1. The MasterList.xlsx file has to be open any time I am using a speadsheet that pulls data from it, or the dropdown lists are blank.
2. I often have to convert the spreadsheets to PDF to send a patient's file to another provider. The PDF software will combine multiple files into one big PDF, converting files to PDF as necessary, but it hangs up when doing this with a file that pulled data from MasterList.xlsx. I can print the spreadsheet to PDF manually, but that takes an extra step and is inefficient.
3. Editing any of the dropdowns invokes an error message of "You may not use references from other workbooks for Data Validation criteria." The list still works, but I always feel like I am courting disaster if this link breaks.

It seems like there should be a more elegant way of creating these dropdown lists. Can anyone suggest a better solution?