Comparing sheet values to external list, flag anything missing

[bleepbloop]

Hello community!

I'm trying to automate the comparison of alphanumeric values in a certain column of a sheet to an external list of what these values *should* be.  That is, I have an external and complete list of the values, and I want to compare what's in individual sheets to this complete list and have Excel automatically flag any missing values.  

So far, I've used a macro to copy / paste the external list into the sheet I can use for comparison, but that relies on having that second sheet up and active.  Since this external list of values is fully static and won't change, I'm hoping there's a way (via macro or otherwise) to automatically compare values found in individual sheets to this external list.

Thanks!

[#2]

You will need a way for Excel to get that list somehow. That could be copying that list to another sheet in the workbooks of interest, creating a defined Name that includes that list, hardcoding the list into a macro/VBA function, or possibly other ideas but somehow Excel needs access to that list. Using VBA you could have it specifically open that file but the problem is you need to open that VBA/Macro and therefore need it in each workbook or in the master workbook anyhow. If you have many different workbooks, to me it seems easy to open your 1 master worksheet and then do comparisons that way as opposed to copying it to every single other workbook. That said, if each 'new' workbook it generated based on a template, then just include that extra sheet or defined name in the template. Depending on the approach, the comparison can be easily done using built in functions and/or conditional formatting.

[#3]

@mtarler 

Amazing thank you!!  I'll pursue those options.  FWIW, I completely agree if I were comparing many workbooks at once having a single comp source would work great but I'm trying to build a tool for many team members to use as-needed in the future.  So, self-contained solutions such as the Name or hard coded into VBA might be the way to go.

Again thank you!!