This is the non-macro'd solution. Due to company procurement policies, you may find yourself having to create data solutions without the help of VBA or Access. I have found myself in such a situation, as I am an accounting technician and not an engineer. Engineers get the fancy solutions, the accounting department does not.
A: Determine where your data source comes from and how it flows. The number of IDs seem to occur with new data so it would be wise to keep your ID reports with a report creating sheet instead of creating multiple reports in multiple sheets.
Multiple tables will need to be built for you to make your reports. Since Excel has limits on the number of sheets you can have, you will need to be careful on how you can fit the tables that will hold all your data.
B: Learn and use the Index functions. SUMIFS, COUNTIFS, AVERAGEIFS functions are your friend as Windows has made these functions to make Excel more powerful over the last decade. You may be familiar with this all ready but. . . it would be wise to remind.
C: Learn to use dropdown lists in your Master Report. Under Data, click on Data Validation and under Data Validation click Data Validation. You will see window pop-up with a drop down list. Select List. The data selection for this drop down list is going to need to use an OFFSET function with a nested COUNTA or COUNT function for the list to obtain the correct length.
The dropdown lists will enable you to select an ID which the table below can use to lookup the information without having to create multiple instances, arduous changes or hassling manual lookups. It also ensures that the data you select in the dropdown is always correct.
I'm sure it's completely possible to write a VBA macro that will, on-demand, look through every entry on the master sheet, and generate a sheet for each entry that does not already have one, optionally filling it with whatever content you wish. If you've never used VBA before, though this will require a lot of research and trial-and-error.
– jpaugh – 2017-09-06T20:28:05.130Also, you can make formula references to cells in other sheets; so the per-entry sheets could reference the yellow cells above in the master sheet; then, if you change it in the master, the per-entry sheet will be updated by Excel "for free." If the row number of the entry (in the master sheet) were stored on the per-item sheet, you go go a step further, and make formulas which choose the row number from a single cell on the template, while having the column baked in.That would make hand-copying the template far less tedious (only the row-num cell needs to change.) A step in the right dir. – jpaugh – 2017-09-06T20:32:26.880
The second option would be preferable as I have no VBA knowledge at all. That said, if the only way to do this efficiently is VBA then so be it. Ill have too learn quick! One side note is that the master sheet in reality has 500 plus entries. Not sure if this would effect the method used. – Lee Roberts – 2017-09-06T20:41:24.947
The template I'm thinking of would only required to copy-paste the template sheet, rename it, then change one cell (the row-num cell) in addition to the green ones. However, it involves some advanced techniques to reference a cell indirectly. This answer should help get you started. In your case, you want a dynamic row number rather than a dynamic sheet name, but the process will be very similar.
– jpaugh – 2017-09-06T20:48:53.290Bear in mind that the template generation step could be added to a VBA macro later, once you got it working. (There's always more to automate.) I wonder whether this whole process might be 1/3 the difficulty of switching to Access, though. 2-5 more database-y problems, and you might reconsider switching. – jpaugh – 2017-09-06T20:51:20.167
It should be noted that for many who do not have access to Access do not have access to VBA. Grumble grumble, company procurement policies. . . I have used excel to do this by using series of lookups and index functions. But my recommendation is to have dropdown lists to select what you desire. – Phillip Siebold – 2017-09-06T21:22:48.867