Hello, I am working on a spreadsheet for work that generates a report for laboratory testing. We do not know in advance how many samples will be analyzed, but for each sample the same 5 worksheets are generated. For example: If there are 5 samples to be tested, there will be a data recorded sheet (samp1), a table (samp1), chart A (samp1), chart B (samp1), and chart C (samp1). The same then goes for each of the other 4 samples: data recorded (samp2), a table (samp2), chart A (samp2), etc. etc..
While I am quite proficient in 'normal' excel, and can brute-force my way into creating these manually (e.g. copying the 5 worksheets and then making the modifications to identify each sheet for its parent sample), I would like to simply run a script that automatically copies the parent sheets for the number of samples that I have based on the inventory tab.
The inventory tab is populated by the user with sample information and identifier. E.g. Sample1, Sample2, Sample3, etc.; with each sample taking one row in the inventory table. The condition for generating a new set of the 5 worksheets would be the addition of another sample in the inventory.
E.g. Inventory:
No samples entered > 5 'blank' sheets currently a part of the workbook
Sample 1 entered > initial 5 sheets still present
Sample 2 entered > CONDITION Triggered >> 5 new sheets, copies of the original 5 are generated at the end of the workbook. Manual editing to connect them with relevant data is fine at this point.
Sample 3 entered > CONDITION Triggered again >> 5 new sheets added after the sheets for sample 2.
Sample 4 entered > CONDITION Triggered again >> ....
Sample 5 entered > CONDITION Triggered again >> ....
Etc.
What I have at this point is a template that includes worksheets for up to 10 samples, where the user can delete un-used worksheets (e.g. in the case where only 8 samples were tested), but at this point, if there are more than 10 samples required, they have to manually copy (and then edit) for as many samples are required.
I realize generating a script that would then correctly 'point' each copied worksheet at their respective data sets would be quite complicated, and likely require some significant coding. I can easily train users to connect each worksheet to its respective data. But the process of having to manually copy multiple sheets for each each sample is very time-consuming and tedious.
Something along the lines of: If (sample added to inventory), then copy/generate 4 worksheets.
I realize I may not have provided enough information for a solution, but would greatly appreciate any help direction the forum has to offer. I've uploaded an image to help clarify.
I would suppose that a similar question to this has already been posted, but was unable to find it when I was searching.
Thank you.
Bob
While I am quite proficient in 'normal' excel, and can brute-force my way into creating these manually (e.g. copying the 5 worksheets and then making the modifications to identify each sheet for its parent sample), I would like to simply run a script that automatically copies the parent sheets for the number of samples that I have based on the inventory tab.
The inventory tab is populated by the user with sample information and identifier. E.g. Sample1, Sample2, Sample3, etc.; with each sample taking one row in the inventory table. The condition for generating a new set of the 5 worksheets would be the addition of another sample in the inventory.
E.g. Inventory:
No samples entered > 5 'blank' sheets currently a part of the workbook
Sample 1 entered > initial 5 sheets still present
Sample 2 entered > CONDITION Triggered >> 5 new sheets, copies of the original 5 are generated at the end of the workbook. Manual editing to connect them with relevant data is fine at this point.
Sample 3 entered > CONDITION Triggered again >> 5 new sheets added after the sheets for sample 2.
Sample 4 entered > CONDITION Triggered again >> ....
Sample 5 entered > CONDITION Triggered again >> ....
Etc.
What I have at this point is a template that includes worksheets for up to 10 samples, where the user can delete un-used worksheets (e.g. in the case where only 8 samples were tested), but at this point, if there are more than 10 samples required, they have to manually copy (and then edit) for as many samples are required.
I realize generating a script that would then correctly 'point' each copied worksheet at their respective data sets would be quite complicated, and likely require some significant coding. I can easily train users to connect each worksheet to its respective data. But the process of having to manually copy multiple sheets for each each sample is very time-consuming and tedious.
Something along the lines of: If (sample added to inventory), then copy/generate 4 worksheets.
I realize I may not have provided enough information for a solution, but would greatly appreciate any help direction the forum has to offer. I've uploaded an image to help clarify.
I would suppose that a similar question to this has already been posted, but was unable to find it when I was searching.
Thank you.
Bob