Forum Discussion
Force Cell Value based on other cells
Enter YES and NO in two cells, e.g. in M1 and M2.
Let's say row 1 contains headers, and you have data in rows 2 to 50.
Select A2:A50. A2 should be the active cell in the selection.
On the Data tab of the ribbon, click Data Validation.
Select List from the Allow drop down.
Enter the following in the Formula box:
=IF(SUM(B2:F2)>0,$M$1,$M$1:$M$2)
Click OK.
This will let users select only YES in column A if the sum of columns B to F is positive, but it doesn't automatically change the value of column A to YES. If you want that, you'd need a bit of VBA code.
Enter YES and NO in two cells, e.g. in M1 and M2.
Let's say row 1 contains headers, and you have data in rows 2 to 50.
Select A2:A50. A2 should be the active cell in the selection.
On the Data tab of the ribbon, click Data Validation.
Select List from the Allow drop down.
Enter the following in the Formula box:
=IF(SUM(B2:F2)>0,$M$1,$M$1:$M$2)
Click OK.
This will let users select only YES in column A if the sum of columns B to F is positive, but it doesn't automatically change the value of column A to YES. If you want that, you'd need a bit of VBA code.