Create base excel template and reflect subsequent enhancements in all old versions

Copper Contributor

Hi Experts

 

I am developing an inventory model in Excel, whereby I need to deliver basic requirements for all countries first and keep adding the enhancements as and when the time goes in the next 6 months or so. For all such enhancements made, I want all the older sheets to be automatically be updated with the new additions/changes made to the base template. I may have about 50 + sheets to be generated from the base template to start with for all countries' warehouses. There will be more enhancements to the base template by adding more columns by adding more logic/more functionalities or correcting bugs etc. and it's a pain to do all these enhancements in the 50+ sheets every time to keep them in sync with the template. May I know the best practice for this requirement? I am sure this may be the requirement for many and there must be some tried and tested best practices in place. Thank you for your great help. 

3 Replies

@prasad1211 In my humble opinion you should not keep 50+ separate sheets if these are subject to frequent updates in lay-out and formulae. Start by keeping all data in one table/database. Create and maintain your report template however you want it. Allow for selecting e.g. location, date range, product or whatever variation you want in your report and then create the desired output on-demand, using the latest template. Perhaps a pivot table can achieve this. Should you need to send it to someone else, create a PDF or copy/paste values (and formats) into a new sheet. Alternatively, create a subset of the selected date to which you can link your template (with formulae). No need to keep old sheets with formulae and old lay-outs. Not sure if I would call this "best practice" though. It's more "common sense".

 

 

Hi @Riny_van_Eekelen 

 

Thanks for your great suggestion.

1. I will try that approach and the only challenge that I may have is the excel limitation for the number of rows. The common database for all products at all warehouse locations in 16 all countries may cross the excel max rows allowable limit. In that case, i may need to maintain 2 transaction databases rather than one common database. I will surely try this option. 

2. I tried to understand the alternate suggestion as you mentioned "Alternatively, create a subset of the selected date to which you can link your template (with formulae).". How to do that? Can you pls help to elaborate a bit more on that? Probably this method might help to have separate subsets but all link to the main template. 

Thank you very much for your great help

 

@prasad1211 Sorry that I was unclear. With the alternative option I had in mind that if it would be required that the remote location would need access to "their data" together with the report, you would extract location specific data out of the master database, include that in the Excel file that contains the report that is based on that data.

 

However, now that you mention the you are potentially dealing with millions of rows of data, I would suggest you look into "Get&Transform Data (a.k.a. Power Query). A powerful tool that allows you to keep your transaction history in one folder (including sub-folders), connect to these dynamically and extract and transform the data based on your specific needs. All this, without the need to physically load everything into Excel first. So, the 1 million row limit is not going to be an issue.

As long as the data structure in all your files is consistent, PQ will be able to connect to them and update whatever analysis you have created with it, automatically.