How do I make the cell to change data after other cell is populated?

Options

I have sheet 1 with the rows below

with formula in:

LAN Migration Ready =COUNTIF({sheet 2 name Range 10})

LAN Migration Ready Approved =COUNTIF({sheet 2 name Range 11})

LAN Migration Scheduled =COUNTIF({sheet 2 name Range 12})

LAN Migration Complete =COUNTIF({sheet 2 name Range 13})

Sheet 2 I have:

Each row represents a Site Location

The trick is when "LAN Migration Ready Approved" is populated in any row in the sheet 2, Sheet 1 Line "LAN Migration Ready" should subtract the "count" because in Sheet 2 a "date" was entered in the "LAN Migration Ready Approved" column.

When Sheet 2, column " LAN Migration Scheduled" a "date" is entered the sheet 1 line "LAN Migration Approved" should subtract the count

When Sheet 2, column "LAN Migration Complete" a "date" is entered, sheet 1 line "LAN Migration Scheduled" should subtract the count

The life cycle is the Migration starts in "LAN Migration Read" then "LAN Migration Ready Approved" then "LAN Migration Scheduled" then finishes in "LAN Migration Complete"

and Sheet 1 should show only the "count" when the next column in Sheet 2 is not populated. If it is populated, then the previous row in Sheet 1 should subtract the count because that entry in sheet 2 column


Any help I will appreciate!πŸ˜€

Rob

Thank you very much for any help!πŸ˜‰

Rob

Best Answers

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    edited 07/28/22 Answer βœ“
    Options

    Hi @Rob_NYC

    Try this:


    Enter this formulas in Migration counts starting from the top:


    =COUNTIF({READY}, 1) - COUNTIF({APPROVED}, 1)



    =COUNTIF({APPROVED}, 1) - COUNTIF({SCHEDULED}, ISDATE(@cell))



    =COUNTIF({SCHEDULED}, ISDATE(@cell)) - [Column2]5



    =COUNTIF({COMPLETE}, 1)


    I named reference ranges as columns in the sheet 2

    You naming a range here:


    Let me know if that works! Good Luck!

    Tomasz Giba

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    Answer βœ“
    Options

    =IF(ISDATE([LAN MIGRATION SCHEDULED]@row), 1, 0)



    Tomasz Giba

Answers

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    edited 07/28/22 Answer βœ“
    Options

    Hi @Rob_NYC

    Try this:


    Enter this formulas in Migration counts starting from the top:


    =COUNTIF({READY}, 1) - COUNTIF({APPROVED}, 1)



    =COUNTIF({APPROVED}, 1) - COUNTIF({SCHEDULED}, ISDATE(@cell))



    =COUNTIF({SCHEDULED}, ISDATE(@cell)) - [Column2]5



    =COUNTIF({COMPLETE}, 1)


    I named reference ranges as columns in the sheet 2

    You naming a range here:


    Let me know if that works! Good Luck!

    Tomasz Giba

  • Rob_NYC
    Rob_NYC ✭✭
    Options

    I am going to try, I will let you know


    Thank you

    Rob

    Thank you very much for any help!πŸ˜‰

    Rob

  • Rob_NYC
    Rob_NYC ✭✭
    edited 07/29/22
    Options

    Tomasz

    I think I got it right, I will let you know


    Rob

    Thank you very much for any help!πŸ˜‰

    Rob

  • Rob_NYC
    Rob_NYC ✭✭
    Options

    Tomasz

    The only thing I need is a formula for sheet2 when I enter a date on the date column the column for check box populate automatically, because I created extra columns for the check box that I will hide from the tracker.

    If you can help me, I will appreciate very much!

    Rob

    Thank you very much for any help!πŸ˜‰

    Rob

  • Tomasz Giba
    Tomasz Giba ✭✭✭✭✭
    Answer βœ“
    Options

    =IF(ISDATE([LAN MIGRATION SCHEDULED]@row), 1, 0)



    Tomasz Giba

  • Rob_NYC
    Rob_NYC ✭✭
    Options

    Tomasz


    Thank you very much for your help! Good job!


    RobπŸ˜€

    Thank you very much for any help!πŸ˜‰

    Rob

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!