I'm trying to find solutions for creating a master template in Excel. We have almost 100 Excel workbooks where data in them varies but the formatting and cell and row layout including header names remains the same. When a row is deleted in one, it needs to be deleted in all of them. When a row header is updated, it needs to be updated in all of them. When conditional formatting changes in one, it needs to be updated in all of them. Get the gist?
Solutions I've researched that won't work:
- Cell linking - only copies the data and not the format; doesn't account for adding/deleting rows/columns
- Format Paint - doesn't work well between different workbooks and still needs to be applied to each workbook individually; doesn't copy conditional formatting; doesn't account for adding/deleting rows/columns
- Power Query - formatting is based on the destination file and there's concern that when refreshed it would delete/overwrite all of the data specific to that workbook; doesn't copy conditional formatting
- Find/replace - not ideal and doesn't work with exact match (when trying to replace AP it makes no distinction between AP or apply)
- MS Access - for business reasons this needs to stay in Excel (i.e. can't use Access or other database programs); this option doesn't address the conditional formatting applied to all of the workbooks
I've seen numerous threads from people looking at how to create a master template in Excel. I get that its not possible (and I have no idea why Microsoft in all its wisdom hasn't created this function yet), but what I'm looking for are possible workarounds. Right now the process can take a couple people several days to update all of the workbooks. Any work arounds that can reduce this to a single person in just a few hours would be fantastic.
