UDF for Dunamic Ranges

Volant
Volant used Ask the Experts™
on
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:F22)"
Line 4:        Level  2                  "SUMPRODUCT(F5:F5,H5:H8)/SUM(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:F12)"
Line 9:        Level  3
Line 10:       Level  3
Line 11:       Level: 3
Line 12:       Level  3
Line 13:       Level  2             "SUMPRODUCT(F14:F17,H14:H17)/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:H22)/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:F22)" 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)/SUM(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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
  try this in H3, H8 ....

=SUMPRODUCT(F4:INDEX(F4:$F$45,MATCH(TRUE,INDEX(A4:$A$46<>A4,0,0),0)-1),H4:INDEX(H4:$H$45,MATCH(TRUE,INDEX(A4:$A$46<>A4,0,0),0)-1))/SUM(F4:INDEX(F4:$F$45,MATCH(TRUE,INDEX(A4:$A$46<>A4,0,0),0)-1))


Kris
Rob HensonFinance Analyst

Commented:
How about populating all cells with a formula that covers the whole range and then deduct sum of the above as it goes down:

=IF(F5="Level 3",0,(SUMPRODUCT(F5:F22,H5:H22)/SUM(F5:F22))-SUM(F$3:F5))

In other words summing the current cumulative total and taking off those that have already been accounted for.

Cheers
Rob H

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial