Allowing text in a cell only if another has text.

[warpathmechanic]

Here is my situation.

I have checkboxes in an excel sheet referenced to G11-G15 respectively

In E16-E20 I have the following:

=IF(AND(G11=TRUE),F11&" Premium","")

So that the text appears only if the Checkbox is checked.

In F16-F20. I am trying to force it where when E16-20 is blank or becomes blank, any text in F16-F20 is removed. If E16-20 has text, then a user can add a decimal number to the cell.

I have tried data validation, IF statements. I get close but its always with a circular reference.

Can Anyone Help?ray:

 

[austin350s10]

you might have to do that in VBA...

 

[Scott Huish]

BTW, the AND and the =TRUE in your formula are not necessary:

This would accomplish the same thing:

=IF(G11,F11&" Premium","")

 

[Scott Huish]

In the WorksheetChange event:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("E16:F20"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        Select Case c.Column
            Case 5
                If c = "" Then c.Offset(, 1).ClearContents
            Case 6
                If c <> "" Then
                    If Not IsNumeric(c) Then
                        MsgBox "Invalid Entry in cell " & c.Address(0, 0)
                        c.ClearContents
                    End If
                End If
        End Select
    Next
Application.EnableEvents = True
End Sub

 

[warpathmechanic]

you might have to do that in VBA...

Ok, I'm fine with that. Any idea how to write that code? I am a complete novice when it comes to the all mighty Visual Basic

 

[Scott Huish]

I just posted the code that should do what you want.

Copy the code above.
Right click on the tab name for the sheet you want this to happen on.
Click on View Code.
Paste into White area.
Hit Alt-q
When you save the workbook, this code will be saved with it. (For Excel 2007 or higher, save as type .xlsm)

 

[warpathmechanic]

In the WorksheetChange event:

In the what? Should I insert a module in the VBA and then add your code? or is there something else I need to insert?

 

[warpathmechanic]

I just posted the code that should do what you want.

Copy the code above.
Right click on the tab name for the sheet you want this to happen on.
Click on View Code.
Paste into White area.
Hit Alt-q
When you save the workbook, this code will be saved with it. (For Excel 2007 or higher, save as type .xlsm)

OK. I deeply apologize. I am working on a slower computer and posts are happening faster than I can reply. Thank you for your patience. Testing now.

 

[warpathmechanic]

Ok after testing the code given to me by HOTPEPPER, it no longer allows anything other than a decimal (and gives me an error message if I do) but when the checkbox is unchecked in the G cells and the text in the column E cells disappears, the text in column F remains.

I have saved as a macro enabled workbook.

I wish I had more input than this but VB is beyond me.

All help is greatly appreciated.

 

[aramsay8880]

=OR(AND(E16="",F16=""),AND(E16<>"",F16=TEXT(F16,"0.0")+0))

This formula will work as a Custom Data Validation for entering decimal numbers in F16. If E16 is blank, then F16 cn only be blank. If E16 is NOT blank, you can enter a decimal number. Make sure the "Ignore blank" checkbox NOT checked.

It will not delete or validate existing data in F16.