I'm trying to set up data validation for a template workbook. I want to use the "list" type of data validation to make it easy for someone to select proper values. The template workbook and all future clones of the workbook will live on my org's SharePoint. I want the data validation list to be a named range called COLOR.
Easy enough. I can do this.
However...
If I end up having 5 copies of this workbook and I want to update that COLOR named range to add a new entry though, it's going to be a pain. I would have to go into each workbook and fix it.
Surely, there has to be a better way.
I tried creating a named range on a separate workbook and using that named range for data validation, but Excel prevents this. I tried creating a named range entity in the template workbook and pointing the named range to another separate workbook. Again, Excel prevents this for data validation.
My latest try is to create a worksheet in my template that has the named ranges and then try to find a way to auto-import the contents of those named ranges from a separate workbook, but I'm stuck on how to do that (without VBA). Any advice?
