Volant
asked on
UDF for Dunamic Ranges
Hello,
I need a UDF to help me with automatically manage the ranges in formulas as per attached Excel spreadsheet.
I have a project plan template which does calculations in categories and subcategories of activities, e.g., % of completion,
look up the latest date of completion of components and give it to a final completion date for a whole category of tasks, etc.
Each block has numbering representing categorization level. For example,
Line 3: Level 1 "SUMPRODUCT(F4:F22,H4:H22) /SUM(F4:F2 2)"
Line 4: Level 2 "SUMPRODUCT(F5:F5,H5:H8)/S UM(F5:F8)"
Line 5: Level 3
Line 6: Level 3
Line 7: Level 3
Line 8: Level 2 "SUMPRODUCT(F9:F12,H9:H12) /SUM(F9:F1 2)"
Line 9: Level 3
Line 10: Level 3
Line 11: Level: 3
Line 12: Level 3
Line 13: Level 2 "SUMPRODUCT(F14:F17,H14:H1 7)/SUM(F14 :F17)"
Line 14: Level 3
Line 15: Level 3
Line 16: Level 3
Line 17: Level 3
Line 18: Level 2 "SUMPRODUCT(F19:F22,H19:H2 2)/SUM(F19 :F22)"
Line 19: Level 3
Line 20: Level 3
Line 21: Level 3
Line 22: Level 3
Line 23: Level 1
I want to be able to copy a formula in a first line of a category block, which will result with automatically adjusted range from the first line till the last line
falling into same Level. In the example above the formula "SUMPRODUCT(F4:F22,H4:H22) /SUM(F4:F2 2)" will pick the range from Line 3 till Line 22
falling into a category of level 1.
The formula pasted into Line 4 "SUMPRODUCT(F5:F5,H5:H8)/S UM(F5:F8)" will pick the range from Line 5 till Line 8 falling into a category of level 2.
Etc.
Thank you
Volant
Template-Formatting.xls
I need a UDF to help me with automatically manage the ranges in formulas as per attached Excel spreadsheet.
I have a project plan template which does calculations in categories and subcategories of activities, e.g., % of completion,
look up the latest date of completion of components and give it to a final completion date for a whole category of tasks, etc.
Each block has numbering representing categorization level. For example,
Line 3: Level 1 "SUMPRODUCT(F4:F22,H4:H22)
Line 4: Level 2 "SUMPRODUCT(F5:F5,H5:H8)/S
Line 5: Level 3
Line 6: Level 3
Line 7: Level 3
Line 8: Level 2 "SUMPRODUCT(F9:F12,H9:H12)
Line 9: Level 3
Line 10: Level 3
Line 11: Level: 3
Line 12: Level 3
Line 13: Level 2 "SUMPRODUCT(F14:F17,H14:H1
Line 14: Level 3
Line 15: Level 3
Line 16: Level 3
Line 17: Level 3
Line 18: Level 2 "SUMPRODUCT(F19:F22,H19:H2
Line 19: Level 3
Line 20: Level 3
Line 21: Level 3
Line 22: Level 3
Line 23: Level 1
I want to be able to copy a formula in a first line of a category block, which will result with automatically adjusted range from the first line till the last line
falling into same Level. In the example above the formula "SUMPRODUCT(F4:F22,H4:H22)
falling into a category of level 1.
The formula pasted into Line 4 "SUMPRODUCT(F5:F5,H5:H8)/S
Etc.
Thank you
Volant
Template-Formatting.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
=IF(F5="Level 3",0,(SUMPRODUCT(F5:F22,H5
In other words summing the current cumulative total and taking off those that have already been accounted for.
Cheers
Rob H