My problem is related to MIN and MAX functions and how to make them as dynamic as possible.
I work as a project manager and am trying to create a doc as automated as possible. Every project could have High Level tasks and sub-tasks (indent to high level tasks). Every sub-task has a start and an end date. High level tasks also have start and end dates and these should be MIN of all sub-tasks for start date and MAX of all sub-tasks for end date.
How to automate the MIN and MAX functions in excel so users don't have to manually drag (up or down) the array/list of start or end dates depending on the amount of sub-tasks a high level task has?
In attached example you have project A and B both with 1 high level task (AI, AII and BI, BII):
High level Task AI (Project A) has 3 sub-tasks: 1, 2, 3. High Level task AII has two sub tasks: 4, 5
High level Task BI (Project B) has 4 sub tasks: 1, 2, 3, 4. High level task BII has two sub tasks: 5, 6.
As stated above high level tasks start date and end date will be MIN and MAX of sub-tasks respectively.
If I do MAX(1,2,3) for AI and then copy formula for BI, it will only max 3 tasks and not 4 sub tasks. What function can be used for MIN and MAX functions to automatically stop/continue counting when no more sub-tasks for that high level task?
I originally made the question to an excel expert who's youtube channel I follow. The answer was to use the following functions for MIN and MAX respectively: =MIN(I6:INDEX(I6:I24,MATCH(TRUE,H6:H24="",0))) and =MAX(J6:INDEX(J6:J24,MATCH(TRUE,H6:H24="",0))) to make sure ranges were dynamic.
I tried the formulas but am obtaining an #VALUE error back and not sure the reason why.
I truly appreciate any support you can give in solving the problem.
Many thanks for any reply and support you can offer to this request, I highly appreciate it.
AGGREGATE calculates position of next blank cell starting from H6 in your entire range H6:H$20. In that case it will be 5. Minus 1 gives number of subtasks = 4. OFFSET returns column with this 4 days starting from cell I6. Apply MIN to it.
Same for MAX. After that you may copy your cells to find another Start/End dates. Just be careful with absolute and relative references in your formulas.