Toogit Social Post

Reena Shah

one year ago
How to Make another column mandatory based on a value set in another column in Excel?

I have two columns in Excel.

Value1 Value2
Yes This become mandatory
No Then Optional

so if the value1 column has Yes then Value2 should become mandatory else it can stay empty. The user should not be allowed to save the sheet if the col1 has yes and col2 is empty.
4 comments, 0 Followers

It should work fine on every row. Just select the rows by dragging over them. If it isn't working , let me know.

by Ankush Singh one year ago

Thanks Ankush, this is working fine on first row. Do i need to type this formula on each row? Thanks for your inputs.

by Reena Shah one year ago

You want to use Data Validation.

On Value2, set the "Allow" criterion to Custom, and then set the Formula to =OR(Value1<>"Yes",NOT(ISBLANK(Value2))). That way, if Value1 is set to Yes, Excel won't let the user clear Value2.

You can also set the same criterion for Value1. Then, the user can only enter "Yes" there if there's already something in Value2.

make sure you replace Value1 and Value2 with the actual cell addresses of those values, e.g. A1 and B1. Alternatively, right-click the each column, select “Name a Range,” and name it.

hope it will solve your problem.

by Ankush Singh one year ago