Tracy van der Schyff

#Office365Challenge So lookups are pretty cool, and yes, it saddens me to have “limitations” in the title, but it’s true. However, read on and I’ll show you some cool tips to get around that!

Related posts:

• Day 92 – Reusable Metadata in SharePoint – Lookups
• Day 282 – Data Cleanup Formulas for SharePoint

In my example I have a list (List 1) with all the data in and a second list (List 2) from which I’d like to create a lookup column to List 1.

In List 1,  (1) I’ve created a column for each type of column and named it as such (for easy identification). In List 2, (2) I then added a lookup column and investigated which columns are not available as lookups.

As you’ll see by the image below, the following columns are available (3 and 4) in List 2 to use as lookups from List 1:

1. Title
2. Single Line of Text
3. Number
4. Date and Time
5. ID, Modified, Created, Version

Columns not available as lookups (5):

1. Multiple Lines of Text
2. Choice
3. Currency
4. Yes/No Check Box
5. Person or Group
6. Hyperlink or Picture
7. Managed Metadata (Term Store)

Pretty sad right? 🙁 (Don’t despair, scroll down)

So in Afrikaans we have a saying: “‘n Boer maak ‘n plan” – A farmer makes a plan. So….. let’s make a plan.

On the original List 1, I added a calculated column. In this calculated column I still could not use the Multiple Lines of Text, Currency, Person or Group, Hyperlink or Picture or the Managed Metadata, BUT I could see the Choice column and the Yes/No column. Which meant I could write a simple formula =[Choice] and =[Yes/No Check Box] – and then display it as text.

Now in List 2, I can lookup to these two calculated columns for the Choice and for the Yes/No columns as they are displayed as text. See in above illustration – number (6).

Voila!! One small step for man……

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.
Keep in mind that these tips and tricks do not only apply to Office 365 – but where applicable, to the overall Microsoft Office Suite and SharePoint.