Anyone who has an alternative formula for this?

SPIPMO

 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

Paul Newcome

Try this:

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

SPIPMO

Hello @Paul Newcome,

It gives me #INVALID VALUE

Thank you!

spipmo

Paul Newcome

How exactly is [Column2]2 being populated?

SPIPMO

Hello @Paul Newcome

[Column2]2 value based on project current duration, days

say current duration is 361 days, 361days / 7days = Week no. 51

Thanks

Paul Newcome

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

SPIPMO

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!

Paul Newcome

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

SPIPMO

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

Paul Newcome

So this is the formula you have in the Target % to date column?

=INDEX([Column4]@row:[Column8]@row, [Column2]4)