Pull data from one column, based on two other column criteria

glitchwizard ✭✭
edited 07/07/22 in Formulas and Functions

Hello, I'm trying to get the data from the 3rd column into a Sheet Summary, based on the current week and year.

Currently as of writing this week is 27, and the year is 2022, so I'm hoping to get the value of zero, and use that in a formula for a sheet summary.

I thought this equation might work:

`=INDEX([WEEK NO]:[SalesVelocity - 4wkRollingAvg], COLLECT([WEEK NO]:[WEEK NO], [WEEK NO]:[WEEK NO], =27, YEAR:YEAR, =2022), 3)`

But it's evaluating to #BLOCKED and I have no idea how to debug that in smartsheet. I can do it in excel because we can break down the calculations 1 step at a time, but here I'm not sure how to do something similar.

I tried building it step by step, but COLLECT doesn't really allow you to use it unless you're using it with another function, making it pretty hard to see what it's returning to then use in the next step.

What am I missing here?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!