Anyone who has an alternative formula for this?
I need the Target % to date column to sum up the values of each activity based on week no (red font). For now i use this formula (see sample data below) as interim solution, problem is the formula will reach the limit if i have more than 30 weeks (usually we set 100weeks in projects).
Thanks you in advance!
spipmo
Answers

Try this:
=INDEX([Column4]@row:[Column8]@row, [Column2]2)


How exactly is [Column2]2 being populated?

Hello @Paul Newcome
[Column2]2 value based on project current duration, days
say current duration is 361 days, 361days / 7days = Week no. 51
Thanks

Are you using a formula or is it manually entered? If it is a formula, can you copy/paste the exact formula here?

Hello @Paul Newcome
Here's are formulas that I'll use to get the Week no & Period (date) based on project duration / dates
Thank you!

Did you change that last [Column2]2 reference to [Column2]4 after inserting those two rows at the top?

yes, to show how week # changes based on current duration. current duration is 32 days  it falls under week no 5
so the Target % to date will sum up the values from week 1 to week 5 (the formula for this am up to  my interim solution will reach it limit if weeks

So this is the formula you have in the Target % to date column?
=INDEX([Column4]@row:[Column8]@row, [Column2]4)
Help Article Resources
Categories
Check out the Formula Handbook template!