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

glitchwizard

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?

Mark Safran

Hi @glitchwizard

Instead of using the COLLECT( ) function to return the 'row_index' for your INDEX( ) function, use it to define the 'range', and then the 'row_index' is just 1 (i.e. the first row in the returned range that meets all of the COLLECT( ) criteria). Try this instead:

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

-MCS

glitchwizard

https://community.smartsheet.com/discussion/comment/335526#Comment_335526

I get a #BLOCKED on that for some reason. Hmm... back to the drawing board. I see your point and it makes sense, but for some reason it's not calculating.

glitchwizard

https://community.smartsheet.com/discussion/comment/335526#Comment_335526

@Mark Safran Turns out you need the = in the equations for the criterion parameter in the COLLECT() function. it's no longer blocked