White text in SharePoint view

Controlling columns in a view on a black background is not something that can done easily out of the box in SharePoint with existing themes. In order to make columns show up as white text on a black background, you can use a calculated column with a DIV as follows.

=IF(Attendance=”Not Attending”,CONCATENATE(“<DIV style=’color=white’>”,”Not Attending”,”</DIV>”),CONCATENATE(“<DIV style=’color=white’>”,Transport,”</DIV>”))

Advertisement

 

Only seven IF statements can be nested within a calculated field in SharePoint.

If you need more than seven statements, then use & as in the following example:

=IF(Itinerary=”Itinerary A”,[Stops A],IF(Itinerary=”Itinerary B”,[Stops B],IF(Itinerary=”Itinerary C”,[Stops C],IF(Itinerary=”Itinerary D”,[Stops D],IF(Itinerary=”Itinerary E”,[Stops E],IF(Itinerary=”Itinerary F”,[Stops F],IF(Itinerary=”Itinerary G”,[Stops G],IF(Itinerary=”Itinerary H”,[Stops H],””))))))))&IF(Itinerary=”Itinerary I”,[Stops I],IF(Itinerary=”Itinerary J”,[Stops J],IF(Itinerary=”Itinerary K”,[Stops K],IF(Itinerary=”Itinerary L”,[Stops L],IF(Itinerary=”Itinerary M”,[Stops M],IF(Itinerary=”Itinerary N”,[Stops N],””))))))

 

Bizarre Behaviour in SharePoint Calculated Columns

Calculated columns within SharePoint come in useful and broadly follow the syntax which is used in Excel functions. But sometimes they exhibit strange behavior. Take the following case. I have a list with items which can be linked to one another and needed to create a view which would display all items which are linked together. This could be done using a formula in a calculated field, except that a hyperlink field cannot be included in a calculated column formula.

So I wrote a little workflow to copy the contents of my hyperlink field to another text field (called ParentLink) and then run my calculated column against this field using the following formula:

=IF(ISNUMBER(FIND(“http”,ParentLink)),(MID(ParentLink,FIND(“=”,ParentLink,1)+1,LEN(ParentLink))),ID)

ParentLink is my text field and so the formula looks to see if it contains a hyperlink, and if yes then it will extract the ID number at the end of the URL. If the key text “http” is not found then it enters the ID of the item. The formula seems to work, correctly displaying the ID numbers of parent items and any child items that are linked to them. However, if an item is edited and closed then, if there is no link, the ID number is not retained but is set to 0. This happens whether the format of the calculated column is text or number.

Currently, my workaround is to populate the ParentLink field either with a hyperlink if it exists, or with the ID of the item if it does not. Then, rather than using the ID field, I just use the ParentLink field as follows:

=IF(ISNUMBER(FIND(“h”,ParentLink)),(MID(ParentLink,FIND(“=”,ParentLink,1)+1,LEN(ParentLink))),ParentLink)

This works, but bizarre behaviour, nonetheless.