Formula errors: "flashing" #BLOCK errors and formula referencing wrong rows

Carmen W

Hi All,

I have some sheets that have some heavy formulas and there are two issues:

• Whenever I update a cell (without formula) the entire sheet just start "flashing" with #BLOCK errors in pretty much all cells with formula and then these errors disappear after 5-10 seconds or so, but that's with every single update. I think it has something to do with the formulas in columns that referencing each other, not to the point that it gives me CIRCULAR errors but to the point it takes longer to for the sheet to process. Is there a way to "turn off" these flashing #BLOCK error when the sheet is processing/loading?

• Sometimes the formula will reference a wrong row. For example, in a cell in row 481, the formula is like the following. So when I copy this cell to say row 491, "480" should be automatically updated to 490. But sometimes it just remains as "480" (or some other numbers if I am doing copy/paste multiple times). Do we know why and is there anything I can do to prevent this? It is a very serious issue because sometimes these wrong formula doesn't give me an error, it gives me a wrong value and I would not have known because there are so many sheets being created/updated everyday by other colleagues and we can't click into all those rows to double check the formula is referencing the correct row, we can only check and fix when there is an error.

=IF(AND(ISNUMBER(INDEX([Row#]:[Row#], MATCH(RowID@row, RowID:RowID, 0) - 1)), NOT(ISBLANK(INDEX(Completed:Completed, MATCH(RowID@row, RowID:RowID, 0) - 1)))), INDEX(Completed:Completed, MATCH(RowID@row, RowID:RowID, 0) - 1), INDEX(Deadline$1:Deadline480, MATCH(RowID@row, RowID:RowID, 0) - 1))

Thanks.

ro.fei

Hey @Carmen W

The issue with formulas showing up as blocked when it shouldn't is pretty common in my experience. I tend to resolve it by refreshing the page. As far as I know, there isn't a reliable way to fix this, it tends to happen when you have a formula referencing other cells with formulas. If you look at the Formula Error Messages page, you'll see that the official cause is "The calculation is blocked because at least one of the cells referenced by the formula has an error." However, you said they tend to resolve themselves, so it seems you're having the issue I'm having (i.e., you don't actually have errors, it just takes time to recognize that), & unfortunately I think the only solution for that is to wait or refresh the page.

As for your copy/paste issue, I think the difference you're running into is whether you're copying the entire cell or just the formula. When you click inside a cell & copy the text/formula inside of it, it will keep the exact string as written (meaning you'll end up with the exact same cell references). For what you're trying to do, make sure you have the cell selected but not clicked open (i.e., you don't want a flashing curser waiting for you to type into the cell). I'll put in some pictures below to help explain:

Copying with the cell selected like this is correct & will have updated cell references:

Copying with the cell clicked open like this is incorrect & will keep the exact cell references as the original:

I hope this helps!! 😊