Change drop-down option in one cell based on data entered in a different cell

sajh78

I am looking for a way to automatically select a drop-down option in one cell based on the data entered into a different cell.

Monthly we track target goals against actual goals. The target goals stay the same throughout the year, however the actual goals are updated monthly. Based on the actual goals, monthy we determine if the project is on track, tracking ahead or tracking behind.

Is there a way to automate the project tracker (on target, tracking ahead, tracking behind) based on the most recent actual goal entry?

Jason Tarpinian

You can do this with a 'nested IF' formula, which means, use lots of IF functions. It'd be something like this:

=IF([Target]@row < [Actual]@row, "Tracking Ahead", IF([Target]@row = [Actual]@row, "On Track", "Tracking Behind"))

So the first IF statement checks if ACTUAL is greater than TARGET, and if it's true, will return "Tracking Ahead". If the first IF statement is false, it will move onto the next logic of IF TARGET is equal to ACTUAL. If that's true, then return "On Track". And if neither of the two is true, if it's neither Tracking Ahead or On Track, we can assume it's "Tracking Behind", so we don't necessarily need a final IF statement for the last option.