Returning a value based on multiple criteria

Annie Lindenberg

I am trying to autofill a summary table using formulas. I am able to autofill the monthly counts using date criteria, but I can't get the total numbers to autofill.

I tried using the below formulas to pull from the two places where I have the counts for total members and total new members of each member type, but I keep getting a #BLOCKED error message that impacts my other formulas.

=INDEX(COLLECT([Associate Healthcare]:[Associate Healthcare], Year:Year, YEAR(TODAY()), Ancestors:Ancestors, 0, New:New, false), 1)

=INDEX(COLLECT([Associate Healthcare]:[Associate Healthcare], Year:Year, YEAR(TODAY()), Ancestors:Ancestors, 0, New:New, true), 1)

Is it not pulling because I already have formulas in the cells the data would be coming from? Or is there another formula I should be using to accomplish this?

David Joyeuse

Hi @Annie Lindenberg

I believe your problem comes from two things.

First the #Blocked error comes from the #circular reference error.

Now, If I understand your screenshot, all these seems to be from the same smartsheet page.

I would rather suggest you to either put your summaries on another sheet for better visibility.

Otherwise, if you want to stick with it, you'll have to rework your formulas and specificly the [Associate Healthcare] range and replace it with:

[Associtate Healthcare]$1:[Associate Healthcare]$26 (or the last row number of your 2019 children). Then adapt the Year and Ancestors range alike so they all have the same range.

Otherwise Smartsheet is trying to go for the full range of the column and here find other stuff that he'll try to calculate to go on with the COLLECT function, but can't because it's depending on the COLLECT function he's trying to calculate.

That should solve the problem. But Smartsheet works best if you're trying to calculate all of these things on a second sheet :)

Hope it helped!

David Joyeuse

Hi @Annie Lindenberg

I believe your problem comes from two things.

First the #Blocked error comes from the #circular reference error.

Now, If I understand your screenshot, all these seems to be from the same smartsheet page.

I would rather suggest you to either put your summaries on another sheet for better visibility.

Otherwise, if you want to stick with it, you'll have to rework your formulas and specificly the [Associate Healthcare] range and replace it with:

[Associtate Healthcare]$1:[Associate Healthcare]$26 (or the last row number of your 2019 children). Then adapt the Year and Ancestors range alike so they all have the same range.

Otherwise Smartsheet is trying to go for the full range of the column and here find other stuff that he'll try to calculate to go on with the COLLECT function, but can't because it's depending on the COLLECT function he's trying to calculate.

That should solve the problem. But Smartsheet works best if you're trying to calculate all of these things on a second sheet :)

Hope it helped!