Excel Conditional Formatting Based on Team Name

Question

I'm looking to create some Excel conditional formatting that shows if a team member met a goal based on the team they are on. In my Team tab, I have a table showing the Employee's name, the team they're on, and all of their stats for the year. When I change employee name, the team name can change. In my Goals tab, I have the goals each employee should hit by quarter for each different team.

What formula can I use in the conditional formatting that will look at the team name and compare it to the goals for that team? I'd like a cell to show as red if a goal is not met. And will this formula update itself when the Team name changes?

Team

Goals

Thanks in advance!

Answer 1

Use the change in Team name to add lookup the goals and put them in the Team spreadsheet on a hidden row. Then use a conditional formula to compare them.

If you put the goals in row 8, then for F18 use Conditional Formatting->Highlight Cells Rules->Less Than... and put =F8 for the condition. Select some red formatting, and it'll show up on all less than the hidden amount.