#BLOCKED om simple formula

Options

Hi,


Newbie to this program so please bear with me.


Basic formula "=Rate@row * [Final time]@row" and it worked for the first few rows and then started populating #BLOCKED. No errors in the other cells so I replicated the same logic in the row below which resulted in #BLOCKED, but the previous row now calculated the right value. What am I missing?


Any help would be appreciated.

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Alex Oldfield

    Hope you are fine, Can share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Alex Oldfield

    How are the values in the two other columns (Rate & Final time) being returned?

    This error message (BLOCKED) generally occurs when one of the cells referenced by the formula has an error. Is it possible that the issue lies within either the Rate or Final Time column?

  • Alex Oldfield
    Options

    Hey,


    I deleted a couple of the rows last night, reopened this morning and entered the same thing and don't appear to be getting the issue. When completing yesterday, as soon as I entered details into the Account No cell , #BLOCKED came up in a Charge cell (that needed more fields to be filled in before a value should populate) where as now it is populating #NO MATCH instead until the last value is entered.


  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Alex Oldfield

    It looks like you have formulas referencing blank cells, which is why it's returning an error in either your Final Time or Rate column. That means any formulas looking at those cells will return an error as well, since it's referencing an error.

    You may want to wrap an IFERROR statement around your references to return 0 if there is no information yet, or if there's a "NOMATCH" error in one of the cells it's referencing:

    =IFERROR(Rate@row, 0) * IFERROR([Final time]@row, 0)

    Let me know if this works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!