Column Value Until Another Column Triggers Change

BESP10 ✭✭✭✭✭✭

Good afternoon,

We currently have our "BAC" column with this formula , =IFERROR(HTD@row / [% Comp.]@row, 0).

However, we need to be able to change it so that the "BAC" column will equal the "BAE" column until the "HTD" and "% Comp." columns have values entered into them.

Thank you in advance for your help!

Best Answer

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓

    I think you just need a nested IF statement. Something like:

    =IF(OR([% Comp.]@row = 0, HTD@row = 0), BAE@row, IFERROR(HTD@row / [% Comp.]@row, 0))

    The first IF and OR statement basically tell the formula to use BAE until both % complete and HTD are not 0. The second comma is the False of that statement (meaning % complete and HTD have something in them) and reverts to your original formula.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!