Joyce Brown
Summary tab contains project name which is the name for remaining tab names in my google sheets as follows:
| TabName |
|---|
| Project1 |
| Project2 |
| Project3 |
There may be up to 20 TabName rows defined but this is not necessary. All project name tabs have the same format - Row 1 contains the weekly start dates and Column 1 has the resource names. The following is what the project name tabs would look like -
Project1 tab
| Person | 1-Mar-2021 | 8-Mar-2021 | 15-Mar-2021 | 22-Mar-2021 | 29-Mar-2021 | 5-Apr-2021 | |||
|---|---|---|---|---|---|---|---|---|---|
| John | 2 | 2 | 4 | 5 | 5 | 5 | |||
| Jane | 1 | 1 | 1 | 1 | |||||
| Abe | 5 | 5 |
Project2 begins at a different Start Week from Project1 and Project3.
Project2 tab
| Person | 4-Jan-2021 | 11-Jan-2021 | 18-Jan-2021 | 25-Jan-2021 | 1-Feb-2021 | 8-Feb-2021 | 15-Feb-2021 | ||
|---|---|---|---|---|---|---|---|---|---|
| Joe | 2 | 2 | 4 | 5 | 5 | 5 | |||
| Mary | 1 | 1 | 1 | 1 | |||||
| Abe | 5 | 5 |
Project3 begins at a different Start Week from Project1 and Project2.
Project3 tab
| Person | 8-Feb-2021 | 15-Feb-2021 | 22-Feb-2021 | 1-Mar-2021 | 8-Mar-2021 | 15-Mar-2021 | 22-Mar-2021 | 29-Mar-2021 |
|---|---|---|---|---|---|---|---|---|
| Joe | 2 | 2 | 4 | 5 | 5 | 5 | ||
| Mary | 1 | 1 | 1 | 1 | ||||
| John | 5 | 5 | ||||||
| Marg | 3 | 3 | 3 |
Note, not all resources are across all projects (e.g. Jane and Marg are only in one project).
In the summary tab, it should show the concatenation of all projects by resource and week as follows -
Resource Weekly tab
| Person | 4-Jan-2021 | 11-Jan-2021 | 18-Jan-2021 | 25-Jan-2021 | 1-Feb-2021 | 8-Feb-2021 | 15-Feb-2021 | 22-Feb-2021 | 1-Mar-2021 | 8-Mar-2021 | 15-Mar-2021 | 22-Mar-2021 | 29-Mar-2021 | 5-Apr-2021 | |||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| John | Project3 (5) | Project1 (2) | Project1 (2) Project3 (5) | Project1 (4) | Project1 (5) | Project1 (5) | Project1 (5) | ||||||||||
| Jane | Project1 (1) | Project1 (1) | Project1 (1) | ||||||||||||||
| Joe | Project2 (2) | Project2 (2) | Project2 (4) | Project2 (5) | Project2 (5) | Project2 (5) Project3 (2) | Project3 (2) | Project3 (4) | Project3 (5) | Project3 (5) | Project3 (5) | Project3 (5) | |||||
| Abe | Project2 (5) | Project2 (5) | Project1 (5) | Project1 (5) | |||||||||||||
| Mary | Project2 (1) | Project2 (1) | Project2 (1) | Project2 (1) | Project3 (1) | Project3 (1) | Project3 (1) | Project3 (1) | |||||||||
| Marg | Project3 (3) | Project3 (3) | Project3 (3) |
Column 1, which contains the resource name and Row 1 with the start dates are fixed (i.e. no calculation required). Also, where a resource has more than one projects, the concatenated projects are separated by a line return.
The concatenation calculation should ideally not perform if the Start Week does not have a date OR if there is no resource name defined for that row. The Summary tab will identify the number of project tabs that exists where the concatenation calculation needs to look for data.
I do have the Resource Weekly tab working using a very long SUMPRODUCT and INDIRECT. However, the google sheet is now running much at least 5 times slower when I added this Resource Weekly sheet.
Is there a way to do this using app script?
cbmserv...@gmail.com
Hi Joyce,
You can make Apps-Script do pretty much anything. I don’t see why it would not be able to run your spreadsheet for you.
However, you did not explain which tab is a view only and which one is used to input info.
Typically Apps-Script would run more efficiently if calculations and updates done are done on a trigger of some type. Like a data entry. Have you considered using a Form to enter the data to make it less error prone?
Thanks.
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ca9394dd-eaf4-4995-8553-c559ab5ffebco%40googlegroups.com.
Joyce Brown
I would like the Weekly to refresh when there is a change in the Project tabs.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-community+unsub...@googlegroups.com.
CBMServices Web
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/ca9394dd-eaf4-4995-8553-c559ab5ffebco%40googlegroups.com.
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. --
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/016ed69f-a282-4a09-8364-07d16316ec06o%40googlegroups.com.