SOLVED

Filling cell based off of specific name in area.

Copper Contributor

Hello,

 

I am managing crews for a company.  I am looking for a formula.  I have a list of employees in row"G".  I am assigning each employee to a crew "A2:D20".  When I assign them to a crew"A2:D20" I would like the employee in row"G" to be highlighted.  Sort of like a check list to make sure every employe is in a crew.  Thank you for an

Screenshot (8).png

4 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@JasonPE509 

You may apply conditional formatting rule with formula

=COUNTIF($A$2:$D$20, $G2)

to $G2:$G50 or so range

That's exactly what I needed. Thank you so much!

@JasonPE509 

But if you put a name into the area on the left that is not in the apprentice list, you will see no visual indication of that.

 

See the attached workbook for alternatives where you assign the crew leader next to the apprentice (so no highlighting of the apprentice list is needed).  And the formulas just beneath each Crew Leader name on the left pull from that list ... or from an Excel table, as on Sheet3:

2023-08-07 JM.jpg

Results for crew leaders with multiple apprentices spill (an Excel term) to cells below the formula.  (The formula is repeated as appropriate beneath the first apprentice, but is grayed out, indicating that you can't change it there.)

 

@JasonPE509 , you are welcome

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@JasonPE509 

You may apply conditional formatting rule with formula

=COUNTIF($A$2:$D$20, $G2)

to $G2:$G50 or so range

View solution in original post