How can I have cell C contents change if cell A changes?

Hey there all,

I don't know exactly how to word my question - but I have 3 rows attached to contacts lists in the SmartSheet. Contact List A, B, and C. See below, for column C as individual's are added to this sheet, we will also add their Coach in column A. But coaches can change with tenure, so what I want to do is take away as much of the manual work from my team as possible by adding a formula which can review column C against all previous entries and compare Column A among those entries. If the newest Column A entry differs from past entries, then Column B for each will be updated to the new Coach.

I hope this makes sense, I've added screenshots below. I figure I can add a automatic column to tag the date and time of the entry, this can be used to track which is the "newest" and compare it to the previous entry.



I was thinking a combined If/Match or even combined Vlookup formula - but I'm just not good enough to think through it for this one. Any help is appreciated!


  • Mike TV
    Mike TV ✭✭✭✭✭✭


    I recommend creating a database sheet of coaches such as this:

    Then you can index match on your active sheet something that looks like this:

    Current Coach formula:

    =INDEX({Coach Name}, MATCH("Active", {Coach Status}, 0))

    {Coach Name} is a cross-sheet reference to the Coach column in my first screenshot and {Coach Status} is a cross-sheet reference to the Status column in the first screenshot.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!