Hello,
I have a spreadsheet I need to modify in a way I'm not sure is possible. I currently have formulas in place to return all 'Projects' (beginning in row A6) that match a selected 'Group' (as indicated in cell B2) and include the actual spending against each 'Project' for the selected time period (as indicated in cell B3). In the example below, since 'Cats' is selected for '2016 Q2', all projects matching that group are returned along with the 'Actual' cost for that period.
<tbody>
</tbody>
Here is an example of the formula in cell A6:
=IFERROR(INDEX('Source Data'!$D$4:$D$19,SMALL(IF(ISNUMBER(MATCH('Source Data'!$B$4:$B$19,$B$2,0)),ROW('Source Data'!$B$4:$B$19)-ROW('Source Data'!$4:$4)+1),ROWS($A$6:$A6))),"")
The challenge is, I have a new requirement where within the group 'Fish' (and only within the group 'Fish'), there are some projects (but not all 'Fish' projects) that should be combined with other projects from other groups (e.g. Cat). For these such projects, the dollars should just be added to the other project. Here are the values that should be combined:
<tbody>
</tbody>
So, Project 1A should have it's $'s added to Project 1 (which appears under 'Cats'). In the example above, if Project 1A had actuals costs of $20k for the selected period, the result for Project 1 should be $55,455.
Project 1A should not be listed anywhere in the results (whether the user selects 'Cats' or 'Fish' which is the original group. Can my formulas in Column A be modified to accommodate for such a requirement? Note that the list of projects requiring combination is fairly short and static. I can easily maintain them in a table on the spreadsheet or, given it's just a few of them, put them directly in the formula by name if there's a way to do that.
Thanks in advance! Sorry to be so long-winded on this request!
I have a spreadsheet I need to modify in a way I'm not sure is possible. I currently have formulas in place to return all 'Projects' (beginning in row A6) that match a selected 'Group' (as indicated in cell B2) and include the actual spending against each 'Project' for the selected time period (as indicated in cell B3). In the example below, since 'Cats' is selected for '2016 Q2', all projects matching that group are returned along with the 'Actual' cost for that period.
| A | B | C | D | |
| 2 | Group | Cats | Data Validation | |
| 3 | Quarter | 2016 Q2 | Cats | |
| 4 | Dogs | |||
| 5 | Project | Actual | Birds | |
| 6 | Project 1 | 35,455 | Fish | |
| 7 | Project 2 | 64,545 | 2016 Q1 | |
| 8 | 2016 Q2 | |||
| 9 | 2016 Q3 | |||
| 10 | 2016 Q4 | |||
| 11 | 2017 Q1 | |||
| 12 | 2017 Q2 | |||
| 13 | ||||
<tbody>
</tbody>
Here is an example of the formula in cell A6:
=IFERROR(INDEX('Source Data'!$D$4:$D$19,SMALL(IF(ISNUMBER(MATCH('Source Data'!$B$4:$B$19,$B$2,0)),ROW('Source Data'!$B$4:$B$19)-ROW('Source Data'!$4:$4)+1),ROWS($A$6:$A6))),"")
The challenge is, I have a new requirement where within the group 'Fish' (and only within the group 'Fish'), there are some projects (but not all 'Fish' projects) that should be combined with other projects from other groups (e.g. Cat). For these such projects, the dollars should just be added to the other project. Here are the values that should be combined:
| 'Fish' Projects to be combined: | ||
| Actual Group | Combination Group | |
| PROJECT 1A | Project 1 | (Cats) |
| Project 4A | Project 4 | (Dogs) |
<tbody>
</tbody>
So, Project 1A should have it's $'s added to Project 1 (which appears under 'Cats'). In the example above, if Project 1A had actuals costs of $20k for the selected period, the result for Project 1 should be $55,455.
Project 1A should not be listed anywhere in the results (whether the user selects 'Cats' or 'Fish' which is the original group. Can my formulas in Column A be modified to accommodate for such a requirement? Note that the list of projects requiring combination is fairly short and static. I can easily maintain them in a table on the spreadsheet or, given it's just a few of them, put them directly in the formula by name if there's a way to do that.
Thanks in advance! Sorry to be so long-winded on this request!
