Tallan's Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

Posts Tagged "formula"

Create Unique Keys Using Calculated Fields

Craig Condon
Custom Software Development

This little trick is something that should have struck me long ago, but I just recently hit upon it. I had a situation where I needed to create a column in a list that looked up data from another list. Normally, lookup columns use the Title field as the text displayed in the lookup dropdown. In my case, this wouldn’t work because the Title field wasn’t unique in the lookup list.
Using a little bit of database design thinking, I decided that the combination of the Title and a Date field presented a unique business key. In the lookup list, I created a calculated field using the following formula (assume field names of Title and Date):
=TEXT([Date],”yyyy-mm-dd”)&” – “&Title
I then created the lookup column in the other list to point to this calculated column instead of the Title column.
The beauty of calculated…

Week Ending Column in SharePoint List

Craig Condon
Portals & Collaboration

I was recently working on an excel pivot table report that pulls its data from a SharePoint list. One of the business requirements was to be able to sort, filter, and/or group by a “Week Ending” date. The list has a column called Action Date which could be any day/date. The solution turned out to be quite simple.
Create a new column in the list called, for example, “Week Ending.” Make this column a calculated column with the following formula:
=TEXT([Action Date]+(7-WEEKDAY([Action Date])),”mm/dd/yyyy”)
Replace [Action Date] with the column off which you are basing the week ending date.
This particular formula assumes that Saturday (7) is the week ending date. Adjust accordingly.

Tallan’s Social Media

LinkedIn
YouTube
SlideShare

Archives

© 2018 Tallan, Inc.
\\\