L
Legacy 226937
Guest
Hey All,
I'm looking for some guidance on how to approach creating worksheets based on a matrix in a table in my workbook. If
Background:
I'm building a reporting tool that runs a few stored procs (Stored on sql server) I built and then based on various user inputs it creates a fancy and unique workbook for one or all site.
Currently, when creating a new workbook for a site, I copy all 8 sheets for the report. Everything is working fine and dandy and I'm super proud of myself
cool: ) with nothing that could stop me!
Goal:
Enter new requirement: copy only the sheets that the site needs based on whether or not they perform the service or not. I store this mapping/matrix in a table named Sites that similar to below (1 = performs service):
<tbody>
</tbody>*Service A is always 1
*There are currently 30 sites
I started using arrays a day or two ago and I'm just learning to use them, and maybe it's excitement of as I feel like this may be overkill/overcomplicating it, but I think the best approach is to store the Sites table in a dynamic array (as it could change in the future - more sites or changes to services) and create a second array to identify which worksheets to generate (each service has 2 worksheets so 9 columns (4 services * 2 sheets + 1 sitename)) - e.g. Site A, Service A = 1, then array2 would have shtServiceASummary and shtServiceADetails and so on. When I generate each report, I would loop through array2 to generate only those that exists in that array.
I was under the impression this may be faster to do all those calculations in the array from what i was reading. Also, I'm not entirely sure that would work since I'm new to arrays haha
Is this the right approach? Any suggestions on a better and easier approach haha Your thoughts would be appreciated. I apologize if it's a dumb question too!
Thanks and happy holidays,
J
I'm looking for some guidance on how to approach creating worksheets based on a matrix in a table in my workbook. If
Background:
I'm building a reporting tool that runs a few stored procs (Stored on sql server) I built and then based on various user inputs it creates a fancy and unique workbook for one or all site.
Currently, when creating a new workbook for a site, I copy all 8 sheets for the report. Everything is working fine and dandy and I'm super proud of myself
cool: ) with nothing that could stop me!Goal:
Enter new requirement: copy only the sheets that the site needs based on whether or not they perform the service or not. I store this mapping/matrix in a table named Sites that similar to below (1 = performs service):
| Site | Service A | Service B | Service C | Service D |
| Site A | 1 | 1 | ||
| Site B | 1 | 1 | 1 | 1 |
| Site C | 1 | 1 | ||
| Site D | 1 | 1 | ||
| Site E | 1 | 1 |
<tbody>
</tbody>
*There are currently 30 sites
I started using arrays a day or two ago and I'm just learning to use them, and maybe it's excitement of as I feel like this may be overkill/overcomplicating it, but I think the best approach is to store the Sites table in a dynamic array (as it could change in the future - more sites or changes to services) and create a second array to identify which worksheets to generate (each service has 2 worksheets so 9 columns (4 services * 2 sheets + 1 sitename)) - e.g. Site A, Service A = 1, then array2 would have shtServiceASummary and shtServiceADetails and so on. When I generate each report, I would loop through array2 to generate only those that exists in that array.
I was under the impression this may be faster to do all those calculations in the array from what i was reading. Also, I'm not entirely sure that would work since I'm new to arrays haha
Is this the right approach? Any suggestions on a better and easier approach haha Your thoughts would be appreciated. I apologize if it's a dumb question too!
Thanks and happy holidays,
J
Last edited by a moderator:
