excel formula

[stilgar]

I'm looking for a formula for a cell like =IF(AND(B17="text"),"no value","")

but i need it so if b17 = "text" then no value is displayed in cell b18 but if b17="more text" then the user can enter their own numerical value into cell b18.

 

[Michael M]

Try

=IF(B17="text","no value","")

However, this will mean that the user can / will overwrite your formula to enter data into B18.
Maybe you could place the furmula in the adjacent cell....say A18

 

[stilgar]

nahh that one won't do it. i don't think i explained it very well.

i have a drop down menu with 2 selections in it.

when "selection1" is selected then the user can enter a numerical value in another cell. say a3. which is then used for other cell formula's.

if "selection2" is selected then whatever value, if any, that is in the cell, eg a3, is removed so that the cell, a3, is empty

 

[Marcelo Branco]

Hi,

Assuming the only possible values in B17 are "Text" or "More text", maybe this

Select B17
Data>Data Validation>Settings-tab and in Allow pick Custom
insert this formula
=OR(B17="Text",B17="More text")

select B18
Data>Data Validation>Settings-tab and in Allow pick Custom
insert this formula
=B17="More text"

Now right-click the sheet-tab pick View Code and paste the code below

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B17"), Target) Is Nothing Then
        Application.EnableEvents = False
        If UCase(Range("B17").Value) = "TEXT" Then
            Range("B18").Value = "no value"
        Else
            Range("B18").Value = ""
        End If
        Application.EnableEvents = True
    End If
End Sub

HTH

M.

 

[stilgar]

that works great but i just need it so when "text" is selected then is removes any value in cell b17

 

[Marcelo Branco]

that works great but i just need it so when "text" is selected then is removes any value in cell b17

Not sure if i understood...

Do you mean that when "text" is entered in B17 it removes any value in B18?

M.

 

[stilgar]

ok i shifted it around a bit but this is what i have

<TABLE style="WIDTH: 499pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=665 x:str><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 173pt; mso-width-source: userset; mso-width-alt: 8411" width=230><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl29 height=20 width=329>No Thanks</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 29pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 width=39></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; WIDTH: 173pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl24 width=230 x:str="" x:fmla='=IF((A1="POWER KIT"),"PLPSK","")'></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl28 width=67></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26>NUMBER OF KITS REQUIRED</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl25> </TD></TR></TBODY></TABLE>

the "no thanks" cell is a drop down menu where you can select "no thanks" or "power kit"

when it's on "no thanks" it should look like the pic above. the "no thanks" cell is b36 with the pic being 4 cells by 2 cells. so "number of kits required" is d37 and the cell where the user enters in the data is e37

<TABLE style="WIDTH: 499pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=665 x:str><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 173pt; mso-width-source: userset; mso-width-alt: 8411" width=230><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl29 height=20 width=329>Power Kit</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 29pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 width=39></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; WIDTH: 173pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl24 width=230 x:fmla='=IF((A1="POWER KIT"),"PLPSK","")'>PLPSK</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl28 width=67></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26>NUMBER OF KITS REQUIRED</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl25> </TD></TR></TBODY></TABLE>


when "power kit" is selected the sheet fills d36 with the code. then the user can enter in the amount they require.

<TABLE style="WIDTH: 499pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=665 x:str><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 173pt; mso-width-source: userset; mso-width-alt: 8411" width=230><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl29 height=20 width=329>Power Kit</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 29pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 width=39></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; WIDTH: 173pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl24 width=230 x:fmla='=IF((A1="POWER KIT"),"PLPSK","")'>PLPSK</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl28 width=67></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26>NUMBER OF KITS REQUIRED</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl25 x:num>78</TD></TR></TBODY></TABLE>

now if the user selects "no thanks" from the drop down menu, the contents of e37 that were entered need to removed. at the moment only the code is removed.

<TABLE style="WIDTH: 499pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=665 x:str><COLGROUP><COL style="WIDTH: 247pt; mso-width-source: userset; mso-width-alt: 12032" width=329><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><COL style="WIDTH: 173pt; mso-width-source: userset; mso-width-alt: 8411" width=230><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" width=67><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffff99; WIDTH: 247pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl29 height=20 width=329>No Thanks</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 29pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 width=39></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc; WIDTH: 173pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl24 width=230 x:str="" x:fmla='=IF((A1="POWER KIT"),"PLPSK","")'></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; WIDTH: 50pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: windowtext 1pt solid" class=xl28 width=67></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27 height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: silver; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl27></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #ffcc99; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26>NUMBER OF KITS REQUIRED</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl25 x:num>78</TD></TR></TBODY></TABLE>

I changed your code a little just to match up with the cells but i think i need to add some sort of clear.contents line to it to do this.

current code is

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B36"), Target) Is Nothing Then
Application.EnableEvents = False
If UCase(Range("B36").Value) = "Power Kit" Then
Range("e36").Value = "No Thanks"
Else
Range("e36").Value = ""
End If
Application.EnableEvents = True
End If
End Sub

all the validation is added in too

 

[Marcelo Branco]

Maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B36"), Target) Is Nothing Then
        Application.EnableEvents = False
        If UCase(Range("B36").Value) = "NO THANKS" Then
            Range("E37").Value = ""
            Range("D36").Value = ""
        Else
            Range("D36").Value = "PLPSK"
        End If
        Application.EnableEvents = True
    End If
End Sub

Dont forget to add the validations
in B36
List
No thanks or Power Kit

in E37
Custom
=B36="Power Kit"

HTH

M.

 

[stilgar]

excellent! works great thanks mate!

 

[Marcelo Branco]

excellent! works great thanks mate!

Glad to help

All the best

M.