In column H1 I use the following to create a number of columns with month data based on the project length (D2) and start date B2 =EDATE($B$2, SEQUENCE(1, $D$2, 0))
The data in A13 is created with the following filter and will be variable in size =FILTER(FILTER(AllStaffProjectAllocationTbl,AllStaffProjectAllocationTbl[Project Name]=A2), {1,1,0,1,0,0,1,0,0,1})
I'd like to automate the data generated from cell H3 in the same way that H1 works, so pasting in one formula that then populates all the subsequent cells from the start of the project (B2) up to the duration of the project (D2).
Here's the forumla in cell H3 that is used to calculate the monthly cost based on the salaries in the resource sumary, and comparing the start and end dates with H1 to see if they are active on the project that month =SUMIFS($E$13:$E$20,$C13:$C20, "<="&H1, $D13:$D20, ">="&H1)
I'd like to find a way to modify this to support the variable Filter size for the resource summary whilst also just pasting in one function in to H3 that will then populate the subsequent cells, modifying H1 to H2 and so on for the duration of the project. Any help will be appreciated.
Additionally, is there a way to populate the column data from G13 downwards from the filter data in A13 such that it has the same names? At the moment I have =A13 and drag it down for the size of the generated filter, it'd be great if this was automated.
CodePudding user response：
The "Active Staff" row can be dynamically calculated with the following formula in cell H2:
=MMULT(SEQUENCE(1,ROWS($A$13#),1,0),($H$1#>=OFFSET($A$13#,0,2,,1))*($H$1#<=OFFSET($A$13#,0,3,,1)))
Both, the data array of A13# and the dates vector of H1# are being addressed as arrays, so the formula above will adapt to any length.
Following the same logic, the "Salary Costs" can be dynamically calculated in H3 as follows:
=MMULT(SEQUENCE(1,ROWS($A$13#),1,0),($H$1#>=OFFSET($A$13#,0,2,,1))*($H$1#<=OFFSET($A$13#,0,3,,1))*OFFSET($A$13#,0,4,,1))
This is pretty much the same formula except for additional multiplication with the "Salary Monthly" vector.
Note that this solution assumes the "Start Date" column of A13 to always be the 3rd column, the "End Date" column to always be the 4th, and the "Salary Monthly" column to always be the 5th. You can see that assumption in the fixed number given to the column index in the OFFSET functions.