Validation List Pull and Push Data to/from Another Workbook

[Mike18640]

I am hoping someone may be able to give me some guidance on setting up a validation list or combo box to update on the fly with items not in its source.

The information in this link works beautifully if the list is stored within the same workbook - http://www.contextures.com/excel-data-validation-add.html.

I'm a commercial real estate appraiser and I run into a lot of redundancy (narrative comments) in my reports for each new property. The problem is that my narrative, although generally similar, can vary slightly from project to project. So, I'd like one workbook to serve as a master list (quasi database) and then a template for each new project that draws from the master list(s). I have basic table and query skills in access, so I am not opposed to learning more and using Access as the database for the master lists of narrative comments. But I would like to keep my front end in Excel.

I've figured out how to pull data from a closed workbook using power query and/or VBA, but I can't figure out to add new items on the fly to the lists in the closed workbook (or even an open workbook). I've Googled it every which way, but I think my problem is that I don't know know exactly what kind(s) of programming I need to make this work.

I really appreciate any help the experts here can offer a small-business owner treading water! Thank you.

-Mike

 

[Mike18640]

I want to update my original post because I found solution from the same source: http://www.contextures.com/excel-data-validation-add-tables.html

Just in case anyone else that writes a lot of similar reports stumbles across my original post.

-- I set up the lists in the "Master" workbook in tables (with headers), spaced at least one column apart.

-- Named ranges pull the tables from the Master into the workbook that I use for each report, which then serve as the source(s) for the validation lists.

-- The Master workbook(s) is/are called during the Open event for the template workbook by running a subroutine that opens all linked workbooks, and then makes them invisible.


It's sloppy, amateur work ... but it works! I'm happy to share my workaround if anyone is ever interested.