excel formula

stilgar

Board Regular
Joined
Feb 28, 2011
Messages
51
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Try
Code:
=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
 
Upvote 0
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
 
Upvote 0
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
Code:
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.
 
Upvote 0
that works great but i just need it so when "text" is selected then is removes any value in cell b17
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Maybe this

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top