# Anyone who has an alternative formula for this?

Options
✭✭✭✭

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).

spipmo

• ✭✭✭✭✭✭
Options

Try this:

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

• ✭✭✭✭
Options

Hello @Paul Newcome,

It gives me #INVALID VALUE

Thank you!

spipmo

• ✭✭✭✭✭✭
Options

How exactly is [Column2]2 being populated?

• ✭✭✭✭
Options

Hello @Paul Newcome

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

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

Thanks

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭
edited 12/02/22
Options

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

• ✭✭✭✭✭✭
Options

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

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!