0

I'm trying to create a filtered lookup from a 'Projects' list that sits on a SharePoint site.

I followed the steps to create a filtered column in a SharePoint list as outlined here: How to make a filtered lookup field

I added a new calculated column named 'Archived' to my lookup list to filter my values. The column type is 'choice' with choices of 'Yes' and 'No'. The calculated column is named 'Filtered Title' and I'm using the following formula:

=IF(Archived,"",Title)

However, instead of blank spaces in my 'Filtered Title' column where the value is 'Yes', I get error '#Value!'.

Would you be able to advise please where I'm going wrong?

Thanks in advance for your time!

Improve this question
1
  • Do you want to show blank value when "Archived" choice column equals to "Yes"?
    – Ganesh Sanap - MVP
    Commented Feb 9, 2023 at 6:45

2 Answers 2

Reset to default
0

IF you want to show blank value for calculated column when "Archived" choice column equals to "Yes", use this formula:

=IF([Archived]="Yes","",[Title])

IF "Archived" column is of type Yes or No (boolean), use this formula (your original formula in question):

=IF([Archived],"",[Title])

Note:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,). For example: If you are using German language for your SharePoint site

Official Documentations:

  1. Calculated Field Formulas
Improve this answer
1

You have to use ISBLANK() to check if the [Archived] column is empty as below

= IF(ISBLANK([Archived])," ",[Title])
Improve this answer
1
  • Thanks Mohamed for the answer. I have tried it and your suggestion removed the 'Value' error but it pulled the wrong Filtered Title, as in pulled the title where it was archived - while I needed it to do the other way round. But otherwise good solution.
    – Marlena
    Commented Feb 9, 2023 at 9:48

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.