Forum Discussion
Assigning different formulas to unique IDs
Hi there everyone! I have a simple table of 8 areas of different sizes where 3 unique teams will work on. The initial start date is manually inputted, the end date is then calculated by taking the start date and adding the amount of days that is required to work on the area.
The subsequent start dates are then calculated as the end date + 1 day by team. Team A can only work in team A areas, where as Team B can only work in team B areas.
I would like to know if there was a way to link the formulas of the subsequent start dates to the teams, so the start date would automatically reference the end date of the area where the team was last working on and add 1 day to get the start date even if the teams have been reassigned to work in different areas.
| Area | Team | Area (m2) | Start Date | Speed (m2/day) | End Date |
| 1 | A | 10 | 1/1/2021 | 1 | 11/1/2021 |
| 2 | B | 23 | 2/1/2021 | 1 | 25/1/2021 |
| 3 | A | 12 | 12/1/2021 | 1 | 24/1/2021 |
| 4 | B | 42 | 26/1/2021 | 1 | 9/3/2021 |
| 5 | C | 30 | 12/3/2021 | 1 | 11/4/2021 |
| 6 | A | 12 | 25/1/2021 | 1 | 6/2/2021 |
| 7 | C | 18 | 12/4/2021 | 1 | 30/4/2021 |
| 8 | B | 20 | 10/3/2021 | 1 | 30/3/2021 |
Example for area 6 I would like to use Team B instead of A, so is there a way to automatically calculate the start date for area 6 if I just change the teams? Area 8 would need to be automatically updated as well as it should reference Area 6's end date now instead of area 4.
Thank you!!!!
Talap1730 I would insert one extra column where you put the initial dates for each team. Then you can calculate the start date for each row with a formula using MAX and MAXIFS for all.
See if the attached file does what you require.
