SOLVED

Force Cell Value based on other cells

Copper Contributor

Force Cell Value based on other cells

Hello,

I am looking for help on how to force a cell to have a specific value only if certain criteria is met, otherwise the cell can have other values.

 A B C D E F YES 500 1000

I want column A to always be "YES" if the sum(B:F)>0, otherwise A can be YES, or NO. How can I accomplish this?

5 Replies

Re: Force Cell Value based on other cells

=IF(SUM(B2:F2)>0,"Yes","No")

Re: Force Cell Value based on other cells

That means the answer is always "no" if the sum is not greater then 0. I want people to be able to type in or select from "Yes" or "No". I think I need to use data validation of some sort but I am not sure how.
best response confirmed by KaylaStarr (Copper Contributor)
Solution

Re: 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.

Re: Force Cell Value based on other cells

Not sure if that idea can help me. I wonder if you know?

Have two cells that must always total 100%

If I put a number into 1 of them, can I make the other one automatically fill in the number to make both equate to 100?

So difficult to find someone who's asked this question.

Thanks

Robert

Re: Force Cell Value based on other cells

That is a different question, it would have been better to ask it in a new discussion.

What you want requires VBA code.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the code listed below into the worksheet module.

Switch back to Excel.

Save the workbook as a macro-enabled workbook (*.xlsm).

Make sure that you allow macros when you open the workbook.

I used B2 and E2 as the two cells. Change the constants in the code to the addresses of the cells you want to use.

``````Private Sub Worksheet_Change(ByVal Target As Range)
' Change these as needed
Const Cell1 = "\$B\$2"
Const Cell2 = "\$E\$2"
If Target.CountLarge > 1 Then Exit Sub
If Target.Address = Cell1 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Range(Cell2).Value = 100 - Val(Range(Cell1).Value)
Application.EnableEvents = True
Application.ScreenUpdating = True
ElseIf Target.Address = Cell2 Then
Application.ScreenUpdating = False
Application.EnableEvents = False
Range(Cell1).Value = 100 - Val(Range(Cell2).Value)
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub``````

Sample workbook attached.

1 best response

Accepted Solutions
best response confirmed by KaylaStarr (Copper Contributor)
Solution

Re: 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.