I am creating a drop-down choice column. For the default, I need to use a value that is based off of a value from another table. Here's the challenge.

In table A, I have a list of organizations. I have a column called "Needs Stuff", it's a one line text field.

In table A, I also created a calculated column called "nsCalc", using the formula: =IF([Needs Stuff]="","N/A","Pending"). Works perfectly.

In table B, I created a lookup from table A and added the columns "A:Needs Stuff" and "A:nsCalc". They show up correctly when viewing table B.

My challange is a drop-down choice column in table B, using a Calculated Value as the Default value using "A:nsCalc":

  • Value from A:nsCalc (default)
  • N/A
  • Pending
  • Approved

So far I am failing. There must be a way, but I am not finding it. Any ideas?

Using SharePoint on O365.

1 Answer 1


You can't do it like this because Lookups are internally based on reference-ids and not the values.

As Formulas can not expand these complex types (same applies to: Multiple Select, [Me] and Person Columns) you can not use them in Calculated Columns

Your workaround is to use Workflows to read the Complex type value and write it to a basic Text column, and then do your magic

  • Great idea! I hadn't thought of that. That might work as I have a workflow in place coying date from one table to the others. I can add this in. Thank you!
    – UbuGray
    Commented Dec 16, 2015 at 17:32
  • Ran into another snag. Because the field in table A is a calculated field and the field in table B is text, the workflow will not copy the value over as the fields are different types.
    – UbuGray
    Commented Dec 16, 2015 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.