tecivo BLOG

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

Tag: IF (page 1 of 2)

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 – Complete=Green, Started=Orange and 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>"

 

Excel Tips…Using VLOOKUP and & to CONCATENATE cells before returning custom text for match/no match found

When using VLOOKUP with data that isn’t made up of unique values you need to combine values to create a unique reference

You can either create additional columns in both data sources and populate with & or CONCATENATE, or you can combine the cell grouping into the VLOOKUP function.

Example – combine Sheet1 columns A and B  to create a unique reference, then use VLOOKUP to search for same in Sheet2 (where an additional column has been created and populated with & or CONCATENATE as appropriate)

=IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),"not found","found")

Sheet1

A B C < C function >
1 Ford Escort found =IF(ISNA(VLOOKUP(A1&B1,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
2 Lotus Elise found =IF(ISNA(VLOOKUP(A2&B2,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
3 BMW 330 found =IF(ISNA(VLOOKUP(A3&B3,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
4 Audi not found =IF(ISNA(VLOOKUP(A4&B4,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
5 Mini Cooper found =IF(ISNA(VLOOKUP(A5&B5,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)
6 Ford Focus not found =IF(ISNA(VLOOKUP(A6&B6,Sheet2!$A:$A,1,FALSE)),”not found”,”found”)

Sheet2

A B C D E < A function >
1 FordEscort Ford US Escort Red =B1&D1
2 LotusElise Lotus UK Elise Silver =B2&D2
3 BMW330 BMW Germany 330 Blue =B3&D3
4 VWGolf VW Germany Golf Black =B4&D4
5 FordMondeo Ford US Mondeo Silver =B5&D5
6 MiniCooper Mini Cooper =B6&D6

Also see

 

Excel Tips…Using VLOOKUP to check if cell value found in second data set

Use VLOOKUP, IF and ISNA to find whether cell value appears in comparison data set.

Example: Search for A1 value in Sheet2 Column A and if found return Found. If not found, return Not Found

=IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),"Not found","Found")

Sheet1

A B < B function >
1 Ford Found =IF(ISNA(VLOOKUP(A1,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
2 Lotus Found =IF(ISNA(VLOOKUP(A2,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
3 BMW Found =IF(ISNA(VLOOKUP(A3,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
4 Audi Not Found =IF(ISNA(VLOOKUP(A4,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)
5 Mini Found =IF(ISNA(VLOOKUP(A5,Sheet2!A:A,1,FALSE)),”Not found”,”Found”)

Sheet2

A B C D E F G
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

Also see

 

Older posts

© 2017 tecivo BLOG

Theme by Anders NorenUp ↑