Generating a column value until another column is filled in

Options
BESP10
BESP10 ✭✭✭✭✭✭
edited 01/26/21 in Formulas and Functions

Good afternoon, I am looking to see if there is even a formula for what we are looking to do. We have an "HRS Left" column that projects the hours left on a scope item with this formula

=SUM(BAC@row - HTD@row)

However!, until we actually start working on that scope we have a "BAE"column that carries our Budget At Estimate hours for that scope item.

Is there a formula that will tell smartsheet to carry the "BAE" column value in the "HRS Left" column UNTIL the "BAC" and "HTD" columns have values?


Please reference attached PDF


Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @BESP10 ,

    You BAC and HTD columns have 0s in them so they're not blank. Change your formula to look for ISBLANK or =0.

    Help?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 01/31/21 Answer ✓
    Options

    Try:

    =IF(OR(ISBLANK(BAC@row), ISBLANK( HTD@row), BAC@row=0, HTD@row=0), BAE@row, BAC@row-HTF@row)

    The OR(...) says that if BAC or HTD are blank or =0, then return BAE. Otherwise return BAC-HTF.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!