Values returned in sheets but report says #BLOCKED

Grant Jones

Hello, I need some help fixing my sheet/formula/report. I have project sheets that all contain a formula to calculate a value. The sheets return the value fine but when i try and run a report for all/some sheets i get #BLOCKED. Even running a report for a single sheet that works, I still get #BLOCKED on the report. Any help would be appreciated.

Happy to post up the formulas, though there are a few of them all saying blocked. They are essentially the same, just grabbing values based on different dates, ie monthly/weekly etc.

EDIT: I should add that I am a hack and have most likely gone about this whole thing backwards.

Melissa Yamada

Hello @Grant Jones

Can you share a screenshot if possible of one of your sheets and the report?

The #BLOCKED error message comes from a formula with at least one of the cells referenced by the formula has an error. You might need to look at each columns with formula and make sure there is no formula error found.

You can see list of formula errors here:

Formula error messages | Smartsheet Learning Center

Grant Jones

Hi Melissa, thanks for your reply. Below are some screenshots. I have created a fresh sheet and added one set of data, then ran the report only for that sheet.

Paul Newcome

Have you tried creating a brand new report?

Grant Jones

Hi Paul, yes Both fresh sheet and report.

This is the formula for "Next Week Revenue Forecast".

=IFERROR(IF(AND(level@row = 1, NOT(ISBLANK([14. Price]@row))), SUM(CHILDREN()), IF(ISBLANK([14. Price]@row), "", [wk1%]@row * [14. Price]@row)), "")

And this is the formula for wk1%

=IF(level@row = 2, SUM(DESCENDANTS()), IF([03. Revenue]@row = "False", "", IFERROR(IF(AND(NOT(ISBLANK([27. End Date]@row)), WEEKNUMBER([27. End Date]@row, 12) = WEEKNUMBER(TODAY(), 12) + 1, [09. Task]@row = "Primary Connections Completed"), [Primary Connections]#, 0), 0) + IFERROR(IF(AND(NOT(ISBLANK([27. End Date]@row)), WEEKNUMBER([27. End Date]@row, 12) = WEEKNUMBER(TODAY(), 12) + 1, [09. Task]@row = "Framing"), [Framing Install]#, 0), 0) + IFERROR(IF(AND(NOT(ISBLANK([27. End Date]@row)), WEEKNUMBER([27. End Date]@row, 12) = WEEKNUMBER(TODAY(), 12) + 1, [09. Task]@row = "Delivery Completed"), Delivery#, 0), 0) + IFERROR(IF(AND(NOT(ISBLANK([27. End Date]@row)), WEEKNUMBER([27. End Date]@row, 12) = WEEKNUMBER(TODAY(), 12) + 1, [09. Task]@row = "Progress Claim"), [Progress Claim]#, 0), 0) + IFERROR(IF(AND(NOT(ISBLANK([27. End Date]@row)), WEEKNUMBER([27. End Date]@row, 12) = WEEKNUMBER(TODAY(), 12) + 1, [09. Task]@row = "Practical Completion"), [Practical Completion]#, 0), 0) + IFERROR(IF(AND(NOT(ISBLANK([27. End Date]@row)), WEEKNUMBER([27. End Date]@row, 12) = WEEKNUMBER(TODAY(), 12) + 1, [09. Task]@row = "Completion"), Completion#, 0), 0)))

Im sure there is a more elegant way to do this.