How to solve #BLOCKED?

Options

Hello,

I recently notice in my spreadsheet that the formulas I've been using stop working after I deleted some rows with data I didn't need anymore.

The formulas are the following:


1st column =MAX(COLLECT([Service End Date]:[Service End Date], [Account Number]:[Account Number], [Account Number]@row))

2nd column =IF([Most Recent Bill Date]@row = [Service End Date]@row, true, false)

3rd column =IF([Most Recent Bill Date]@row < (TODAY() - 45), true, false)

Why is this happening? How can I fix it ? And how can I avoid this error from happening again?

NOTE: when I copy the formulas on a another spreadsheet they work fine.

Thank you,

Alfredo Arrieche

Answers

  • Smartsheet User 99
    Options

    Is service end date, or the account number info being pulled from another sheet? Do any of those columns say #Unparseable? The only way you'll get a #BLOCK statement is if either the service end date or account number column is messed up.

    Luckily, all you'll need to do is fix 1st column, and columns 2 and 3 will fix itself since they are "linked" to column 1.

    I see the note about it working perfectly fine on other spreadsheets. Did you delete any information from those spreadsheets as well? If you didn't then that's why the formulas are still working, and luckily you can compare the spreadsheet the formulas working on vs the one that's not working and see what you accidentally deleted.

    Just drew up something to resemble what I think you have.

  • Alfredo Arrieche L.
    Options

    Both service end date and account columns are not being pulled from another sheet. They are type in the same sheet.

    About what I mentioned working on another sheet, I simply use the option copy to another sheet. and use the same formulas and it worked.