Hello,
First off - I want to thank you all in advance for your responses. I've learned a lot from the advice and wisdom shared with me from this community.
Last year I created a template workbook. The template is more complex than it is simple (unfortunately), mainly because the work I'm involved with is complicated, even at its most basic level. The template workbook was adapted to 150 different engagements / clients.
This year, I'm tasked with updating the template and subsequently pushing through the updates to all 150 engagements. This is far easier said than done. I can make updates to the template easily, but the challenge is how to get these updates reflected in 150 different files. For instance, say I add a formula in column AA in the template. The column AA however may be column AB in one file, column AD in another file, and so on because each file has been modified to some extent to accommodate various facts and circumstances. Small things, like the location of a column or input cell, throws a wrench in the gear.
The nature of a "template" is to be adaptable. What I can't seem to find an efficient way of doing is how to adapt changes in the template to 150 different files, all of which have been modified in one way or another (i.e. adding additional sheets, adding or renaming columns or rows).
Making good use of the name manager is helpful to an extent. Once a column or row is misaligned from the same column or row in the template, copying and pasting doesn't transfer named range references correctly. Not to mention that the template itself is designed to be dynamic (custom filter and sort functions, dynamic lookup values, etc.). I know some VBA code, but I'm in no position to dive into macros right now. The workbooks are shared with many people, and macros may not function correctly on someone else's Excel.
I have to keep in mind that my co-workers have very, very basic knowledge of Excel. Outside of teaching them how to write formulas (not practical at all), I don't know how to proceed. I also have to keep in mind that time matters. If one change takes 5 minutes to implement / update in a workbook, then the total time it'll take for all 150 files is 12.5 hours!
I'm posting this to gather any recommendations, advice, tools, resources, etc. --- Have you been in a similar situation with pushing updates across many files? Is there a tool or resource I can use to make this process more efficient? What would you tell your boss, clients, co-workers who don't have the same understanding of Excel?
Again, many thanks for reading and sharing your input.
First off - I want to thank you all in advance for your responses. I've learned a lot from the advice and wisdom shared with me from this community.
Last year I created a template workbook. The template is more complex than it is simple (unfortunately), mainly because the work I'm involved with is complicated, even at its most basic level. The template workbook was adapted to 150 different engagements / clients.
This year, I'm tasked with updating the template and subsequently pushing through the updates to all 150 engagements. This is far easier said than done. I can make updates to the template easily, but the challenge is how to get these updates reflected in 150 different files. For instance, say I add a formula in column AA in the template. The column AA however may be column AB in one file, column AD in another file, and so on because each file has been modified to some extent to accommodate various facts and circumstances. Small things, like the location of a column or input cell, throws a wrench in the gear.
The nature of a "template" is to be adaptable. What I can't seem to find an efficient way of doing is how to adapt changes in the template to 150 different files, all of which have been modified in one way or another (i.e. adding additional sheets, adding or renaming columns or rows).
Making good use of the name manager is helpful to an extent. Once a column or row is misaligned from the same column or row in the template, copying and pasting doesn't transfer named range references correctly. Not to mention that the template itself is designed to be dynamic (custom filter and sort functions, dynamic lookup values, etc.). I know some VBA code, but I'm in no position to dive into macros right now. The workbooks are shared with many people, and macros may not function correctly on someone else's Excel.
I have to keep in mind that my co-workers have very, very basic knowledge of Excel. Outside of teaching them how to write formulas (not practical at all), I don't know how to proceed. I also have to keep in mind that time matters. If one change takes 5 minutes to implement / update in a workbook, then the total time it'll take for all 150 files is 12.5 hours!
I'm posting this to gather any recommendations, advice, tools, resources, etc. --- Have you been in a similar situation with pushing updates across many files? Is there a tool or resource I can use to make this process more efficient? What would you tell your boss, clients, co-workers who don't have the same understanding of Excel?
Again, many thanks for reading and sharing your input.
