#Office365Challenge It’s not always possible to mark fields as compulsory, should you want a user to complete one of two columns – it becomes more tricky. See formula below to help you achieve this.
| Day: | 286 of 365, 79 left |
| Tools: | Office 365, SharePoint |
| Description: | SharePoint List Validations – Force completion of one of two columns |
| Audience: | All |
Related Posts:
Day 285 – SharePoint List Validations – Force field completion
In my example I have a Cell phone number and a Landline number field. I want the user to only complete one of these fields – which means I cannot mark them as compulsory.
In your list settings under Validation Settings, add the following formula:
=COUNTA([Column1],[Column2])>=1
This value returns the value of “non blank” values for the two columns. It cannot exceed more than 1 blank value and will show the customized message compiled at the bottom:
Below you’ll see the error of the fields are not completed:
Love it!!
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.
April 23, 2020 at 5:24 pm
Can you autocomplete a column with this feature? What is the formula? For example, I have a Status column and a Completed Date column, when you selected ‘completed’, I would like today’s date to populate in the Completed Date field?
April 23, 2020 at 6:03 pm
If you’re in Office 365 you can definitely do that by building a Power Automate. But check out the calculated columns to see if this is possible as well?