All,
Hoping someone can help me out here. Here's my need.
What follows is a simplified explanation of my spreadsheet. I have two sheets ASSIGNMENTS and AVAILABLE CAPACITY
The ASSIGNMENTS sheet has a list of staff members assigned to every project we are working on. There are as many rows for a project as there is staff assigned, e.g. 3 rows for Project 1 to accommodate Jan, Leanne and Dave. Each row has the following columns: project name, staffer name, jan hrs assigned, feb hrs assigned, etc. through 2017. A staffer can appear in the sheet multiple times when assigned to different projects. It looks like this:
Excel 2010
<tbody>
</tbody>
On the CAPACITY sheet I have one, and only one, row for each staffer. Each row has three sections: TOTAL CAPACITY vs ASSIGNED CAPACITY vs AVAILABLE CAPACITY. The sections have columns for each month from 2015 through 2017. For this question my interest is in creating sparklines for AVAILABLE CAPACITY only. It looks like this:
Excel 2010
<tbody>
</tbody>
What I want is to have a column in the ASSIGNMENTS sheet that shows a sparkline of the available capacity for each staffer. So in the examples above the sparkline tool would look at the name in each row in ASSIGNMENTS, say Jan Y, and bring back her sparkline based on Jan Y's capacity on the CAPACITY sheet. I'd prefer that the sparkline automatically adjust to show the next 12 months,e.g. on august 1 the sparklines show aug plus 11 more months. If automatic is too difficult I'm okay with doing a manual update each month of some sort (but not to every cell obviously).
My research suggests that this will require a dynamic named range. This person had part of the solution but it wasn't quite right. Basically, I'm now stuck but I KNOW this is possible with your help. Thanks in advance!
Matt
Hoping someone can help me out here. Here's my need.
What follows is a simplified explanation of my spreadsheet. I have two sheets ASSIGNMENTS and AVAILABLE CAPACITY
The ASSIGNMENTS sheet has a list of staff members assigned to every project we are working on. There are as many rows for a project as there is staff assigned, e.g. 3 rows for Project 1 to accommodate Jan, Leanne and Dave. Each row has the following columns: project name, staffer name, jan hrs assigned, feb hrs assigned, etc. through 2017. A staffer can appear in the sheet multiple times when assigned to different projects. It looks like this:
Excel 2010
| Q | R | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 4 | 3RD GROUPING (2nd grp + Rel# + TargetMo) | Assignments | SPARKLINE SHOWING CAPACITY (NEXT 12 Mos) | Jan-2015 | Feb-2015 | Mar-2015 | Apr-2015 | May-2015 | Jun-2015 | Jul-2015 | Aug-2015 | Sep-2015 | Oct-2015 | Nov-2015 | Dec-2015 |
| 42 | FRONTIER (Nov-'15) (Rel 1 of 3) | Jan Y | 30 | 30 | 30 | 30 | 30 | 30 | 30 | ||||||
| 43 | FRONTIER (Nov-'15) (Rel 1 of 3) | Leanne B | 34 | 34 | 34 | 8 | 8 | 8 | 8 | ||||||
| 44 | FRONTIER (Nov-'15) (Rel 1 of 3) | Dave L | 34 | 34 | 34 | 8 | 8 | 8 | 8 |
<tbody>
</tbody>
Staff Assignments
On the CAPACITY sheet I have one, and only one, row for each staffer. Each row has three sections: TOTAL CAPACITY vs ASSIGNED CAPACITY vs AVAILABLE CAPACITY. The sections have columns for each month from 2015 through 2017. For this question my interest is in creating sparklines for AVAILABLE CAPACITY only. It looks like this:
Excel 2010
| C | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 6 | NAME | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| 13 | Dave L | 45 | 126 | 150 | 150 | 142 | 150 | 150 | 158 | 174 | 166 | 150 | 184 |
| 19 | Jan Y | 48 | 109 | 133 | 133 | 101 | 109 | 109 | 63 | 122 | 114 | 91 | 139 |
| 29 | Leanne B | 42 | 126 | 150 | 150 | 142 | 150 | 150 | 141 | 156 | 148 | 150 | 184 |
<tbody>
</tbody>
Staff Capacity
What I want is to have a column in the ASSIGNMENTS sheet that shows a sparkline of the available capacity for each staffer. So in the examples above the sparkline tool would look at the name in each row in ASSIGNMENTS, say Jan Y, and bring back her sparkline based on Jan Y's capacity on the CAPACITY sheet. I'd prefer that the sparkline automatically adjust to show the next 12 months,e.g. on august 1 the sparklines show aug plus 11 more months. If automatic is too difficult I'm okay with doing a manual update each month of some sort (but not to every cell obviously).
My research suggests that this will require a dynamic named range. This person had part of the solution but it wasn't quite right. Basically, I'm now stuck but I KNOW this is possible with your help. Thanks in advance!
Matt
