Cell Linking

Is there any way to stop a cell link from updating at once it has been populated?

I am using the INDEX/MATCH formulas to assign a person to a task based off of a state assignment list. Once the task is completed, I do not want that person's name to change, even if the state assignment changes in the future. Any help is appreciated, thanks!

Tags:

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Paul Newcome

    forgot about that...

    @MeganJones

    See below slightly revised process:


    State assignment Sheet would have these columns:

    LINE-ID (System Auto Number)

    ROW# Column Formula =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)

    State

    Assignee

    Date


    Task Sheet would have these columns:

    Task

    Task Date

    State

    Assignee

    Assignee would have a formula something along these lines. Change the ranges to actual names

    =INDEX({Assignee sheet - Assignee range},MAX(COLLECT({Assignee sheet - ROW#},{Assignee sheet - State range},[State]@row,{Assignee sheet - Date range},<=[Task Date]@row)))

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!