Home

Making a roster highlighting the people and vehicles that have already been used

%3CLINGO-SUB%20id%3D%22lingo-sub-389944%22%20slang%3D%22en-US%22%3EMaking%20a%20roster%20highlighting%20the%20people%20and%20vehicles%20that%20have%20already%20been%20used%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389944%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20make%20a%20spreadsheet%20where%20the%20employees'%20names%20are%20put%20into%20the%20roster%20and%20their%20names%20highlight%20or%20do%20something%20so%20they%20are%20not%20picked%20again%20and%20double%20up%20anywhere%20the%20same%20with%20the%20vehicles.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attachment%20is%20an%20example%20of%20what%20I%20am%20trying%20to%20sort%20out%20on%20a%20smaller%20scale%20so%20you%20can%20understand%20what%20I%20am%20trying%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20you%20can%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EBill2628%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-389944%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390721%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20a%20roster%20highlighting%20the%20people%20and%20vehicles%20that%20have%20already%20been%20used%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390721%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F309630%22%20target%3D%22_blank%22%3E%40Bill2628%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20added%20another%20condition%20to%20highlight%20the%20cell%20if%20there%20are%20duplicates.%20As%20per%20your%20original%20requirement%2C%20the%20cell%20in%20the%20master%20list%20highlights%20in%20green%20if%20a%20particular%20value%20from%20the%20list%20is%20chosen.%20If%20the%20value%20is%20repeated%2C%20the%20value%20in%20the%20master%20list%20is%20highlighted%20in%20red.%20I've%20attached%20the%20edited%20file.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100514iDD5AB2317D929D1E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ConditionalFormatting_excelroster_2.png%22%20title%3D%22ConditionalFormatting_excelroster_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390584%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20a%20roster%20highlighting%20the%20people%20and%20vehicles%20that%20have%20already%20been%20used%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390584%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F305847%22%20target%3D%22_blank%22%3E%40karthickrichard%3C%2FA%3E%3C%2FP%3E%3CP%3EHello%2C%20thank%20you%20for%20the%20help%2C%20got%20it%20working%20and%20it%20is%20great.%20But%2C%20how%20do%20I%20make%20it%20highlight%20a%20different%20colour%20if%20there%20is%20more%20than%20one%20instance%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20example%2C%20I%20have%20used%20the%26nbsp%3B%3CEM%3EFormat%20only%20unique%20or%20duplicate%20values%26nbsp%3B%3C%2FEM%3Erule%2C%20however.%20I%20would%20like%20to%20have%20the%20values%20that%20have%20been%20duplicated%20highlighted%20on%20the%20original%20side%20if%20possible.%20If%20not%2C%20this%20works%20great%20anyway.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390267%22%20slang%3D%22en-US%22%3ERe%3A%20Making%20a%20roster%20highlighting%20the%20people%20and%20vehicles%20that%20have%20already%20been%20used%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390267%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F309630%22%20target%3D%22_blank%22%3E%40Bill2628%3C%2FA%3E%26nbsp%3B%2C%20you%20can%20use%20conditional%20formatting%20to%20make%20this%20happen.%20Please%20see%20the%20example%20below.%20%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100431iA534B7877681A701%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ConditionalFormatting_excelroster.png%22%20title%3D%22ConditionalFormatting_excelroster.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20formula%20for%20reference.%20%22%3DIFERROR(NOT(IFERROR(VLOOKUP(%24H11%2C%24K%2411%3A%24K%2415%2C1%2C0)%2C1))%2C1)%22%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bill2628
New Contributor

Hello

 

I am trying to make a spreadsheet where the employees' names are put into the roster and their names highlight or do something so they are not picked again and double up anywhere the same with the vehicles.

 

The attachment is an example of what I am trying to sort out on a smaller scale so you can understand what I am trying to.

 

I hope you can help.

 

Regards

Bill2628

3 Replies

@Bill2628 , you can use conditional formatting to make this happen. Please see the example below. ConditionalFormatting_excelroster.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here's the formula for reference. "=IFERROR(NOT(IFERROR(VLOOKUP($H11,$K$11:$K$15,1,0),1)),1)"

Highlighted

@karthickrichard

Hello, thank you for the help, got it working and it is great. But, how do I make it highlight a different colour if there is more than one instance?

 

In the attached example, I have used the Format only unique or duplicate values rule, however. I would like to have the values that have been duplicated highlighted on the original side if possible. If not, this works great anyway.

 

Cheers

@Bill2628 

 

I've added another condition to highlight the cell if there are duplicates. As per your original requirement, the cell in the master list highlights in green if a particular value from the list is chosen. If the value is repeated, the value in the master list is highlighted in red. I've attached the edited file.  

 

ConditionalFormatting_excelroster_2.png

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies