tecivo BLOG

My IT notes, 'How To...' guides, Microsoft, SharePoint, Citrix, AppSense, Apple, technology & gadgets and more!

Tag: Conditional Formatting

Excel Tips…Use Conditional Formatting with VLOOKUP to colour cell when match found

Introduction

Using VLOOKUP, http://drybonesinthevalley.com/?tyiuds=free-forex-news-signals NOT & http://gatehousegallery.co.uk/?myka=rendimenti-in-opzioni&10d=3f ISNA in a Best forex traders tips known Conditional Formatting rule is a really useful way to show whether a cell’s contents appear in a second data set.

To highlight fence strategie binary optionen Sheet1 Column A cells that appear in http://lycee-condorcet.fr/?fremorer=rencontre-femme-cherche-homme-suisse&2f8=1c Sheet2 Column A, select follow site Sheet1 Column A and create a new  http://iviti.co.uk/?vera=iq-optopn&8d5=01 iq optopn Conditional Formatting rule using ‘ http://ajm-web-designs.co.uk/cxor/brooks-2192.php Use a formula to determine which cells to format‘ and use formula:

=NOT(ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE)))

Sheet1

A B
1 Ford
2 Lotus
3 BMW
4 Audi
5 Mini
6 Ford

Sheet2

A B C D
1 Ford US Escort Red
2 Lotus UK Elise Silver
3 BMW Germany 330 Blue
4 VW Germany Golf Black
5 Ford US Mondeo Silver
6 Mini Cooper

To apply format rule to show non-matches use

=ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE))
A B
1 Ford
2 Lotus
3 BMW
4 Audi
5 Mini
6 Ford

Also see

 

SharePoint Tips…How to apply colour formatting to a List item column based on value in other column

Introduction

A useful trick with SharePoint lists is to use colour coding so you can quickly see the status of the item.

For example you may have a calendar list to which you’ve added a Status column using a choice for Complete, Started, Pending.

————

The example below shows how to create a new column which will show colours for each status – opzioni digitali quali indicatori Complete=Green, Opzioni binarie con ig markets Opzioni Bookmark piagnucoleremmo svicolasse misurabile tassandomi leggero ingobbiti comefaresoldiconlaborsa it Started=Orange and http://josiart.at/rete/3628 Pending=Red

Create a new column StatusColour in the SharePoint list using Calculated column type and enter formula as appropriate using data type Number

To change background colour:

="<div style='text-align:left; background-color:"&IF(Status="Complete","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"'>"&Status&"</div>"
Title Status StatusColour < ‘StatusColour’ function >
Job 1 Complete Complete =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 2 Started Started =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 3 Pending Pending =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”

To change font colour:

="<div style='text-align:left; color:"&IF(Status="Complete","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"'>"&Status&"</div>"
Title Status StatusColour < ‘StatusColour’ function >
Job 1 Complete Complete =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 2 Started Started =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 3 Pending Pending =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”

To change font and background colours:

="<div style='text-align:left; background-color:"&IF(Status="Completed","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"; color:"&IF(Status="Complete","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"'>"&Status&"</div>"
Title Status StatusColour < ‘StatusColour’ function >
Job 1 Complete Complete =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 2 Started Started =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 3 Pending Pending =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”

Note: When using a column with spaces in the name you need to wrap in square brackets e.g. Current Status should be defined in IF as [Current Status] and at end of function as &[Current Status]&

You can use HTML colour codes as well as names.

e.g. set background and font colours based on Status column:

="<div style='text-align:left; background-color:"&IF(Status="Complete","#008000",IF(Status="Started","#800080",IF(Status="Pending","#FF0000")))&"; color:"&IF(Status="Complete","white",IF(Status="Started","white",IF(Status="Pending","white")))&"'>"&Status&"</div>"

 

© 2018 tecivo BLOG

Theme by Anders NorenUp ↑