Hi all,
I'm attempting to consolidate a big block of data with two columns and a reference to a month into a summary table and I can't seem to get there.
Essentially I want to sum for example, all the items in the reference table that are CAPEX and Labour for the Month of September into the summary table in C2.
Instead of merging cells D1 and E1 previously I was using OFFSET and I had duplicated the month into both columns but it probably isn't the right way to go about it and I can't figure out how to get it to differentiate between Forecast and Actual because I wrote that formula over a year ago...
(To add a layer of complexity - is there also a way to choose the entire Actuals column over the Forecast column if there are Actuals loaded in the column? So for example I won't have Actuals in the November column yet because it's not November - but if I did can I sum that column instead? Knowing also that not every row is always accounted for each month as resources come and go. If not, I can remove the forecast after entering actuals nbd)
I've been working on this on and off for a week and I'm admitting defeat now! Any help would be more than much appreciated.
Ref data table:
<tbody>
</tbody>
Summary table:
<tbody>
</tbody>
I'm attempting to consolidate a big block of data with two columns and a reference to a month into a summary table and I can't seem to get there.
Essentially I want to sum for example, all the items in the reference table that are CAPEX and Labour for the Month of September into the summary table in C2.
Instead of merging cells D1 and E1 previously I was using OFFSET and I had duplicated the month into both columns but it probably isn't the right way to go about it and I can't figure out how to get it to differentiate between Forecast and Actual because I wrote that formula over a year ago...
(To add a layer of complexity - is there also a way to choose the entire Actuals column over the Forecast column if there are Actuals loaded in the column? So for example I won't have Actuals in the November column yet because it's not November - but if I did can I sum that column instead? Knowing also that not every row is always accounted for each month as resources come and go. If not, I can remove the forecast after entering actuals nbd)
I've been working on this on and off for a week and I'm admitting defeat now! Any help would be more than much appreciated.
Ref data table:
| Sep-18 | Oct-18 | Nov-18 | ||||||
| CAPEX / OPEX | Category | Vendor/Description | Forecast | Actual | Forecast | Actual | Forecast | Actual |
| OPEX | Travel | Travel | $ 1,162.00 | $ 1,200.00 | $ 2,400.00 | |||
| OPEX | Training | Training | $ 10,000.00 | |||||
| OPEX | Prof. Services | Professional Services (Marketing) | $ 6,445.00 | $ 45,000.00 | ||||
| OPEX | Legal | External Legal | $ 1,646.00 | $ 5,000.00 | ||||
| OPEX | Resource | Change | $ 7,402.00 | $ 9,825.00 | $ 9,825.00 | |||
| CAPEX | Labour | Resource 1 | $ 93,537.00 | $ 20,400.00 | $ 15,300.00 | |||
| CAPEX | Labour | Resource 2 | $ 23,161.60 | $ 23,161.60 | ||||
| CAPEX | Labour | Resource 3 | $ 5,000.00 | $ 3,000.00 | ||||
| CAPEX | Labour | Resource 4 | $ 11,040.00 | $ 11,040.00 | ||||
| CAPEX | Labour | Resource 5 | ||||||
| CAPEX | Labour | Resource 6 | ||||||
| CAPEX | Labour | Resource 7 | ||||||
| CAPEX | Labour | Resource 8 | $ 10,000.00 | |||||
| CAPEX | Labour | Resource 9 | ||||||
| CAPEX | Vendor | Email issue | ||||||
| CAPEX | Vendor | 3rd party implementation | $ 11,000.00 |
<tbody>
</tbody>
Summary table:
| Type | Category | Sep-18 | Oct-18 | Nov-18 |
| CAPEX | Labour | |||
| CAPEX | Vendor | |||
| CAPEX | Software | |||
| CAPEX | Hardware | |||
| OPEX | Resource | |||
| OPEX | Training | |||
| OPEX | Professional Services | |||
| OPEX | Legal | |||
| OPEX | Maintenance | |||
| OPEX | Travel | |||
| OPEX | Other |
<tbody>
</tbody>
