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

Jgomez

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.

Before:

After:

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

@Jgomez

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.