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.
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!
Answers
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!