SOLVED

# Filling cell based off of specific name in area.

Copper Contributor

# Filling cell based off of specific name in area.

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

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

# Re: Filling cell based off of specific name in area.

You may apply conditional formatting rule with formula

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

to \$G2:\$G50 or so range

# Re: Filling cell based off of specific name in area.

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

# Re: Filling cell based off of specific name in area.

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:

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.)

# Re: Filling cell based off of specific name in area.

@JasonPE509 , you are welcome

1 best response

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

# Re: Filling cell based off of specific name in area.

You may apply conditional formatting rule with formula

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

to \$G2:\$G50 or so range