If/then

[craigwojo]

In cell "B1" I have a data validation (LIST). The validation list consists of the data "A,B,C,D,E,F,G" (within the drop-down).

I want cell D1 to auto-insert data from the drop-down list in "B1" For instance... If I select "B1" with the data "A", I would like to have "D1" fill with the word "yellow". If I choose "B" in the drop-down list, "D1" would fill with the data "green", ect..

I've done this before but forgot how do do it, I been off excel for a couple of years and being an "every once in a while" user I forget some things.

Thank you and God bless,

Craig

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Insert additional "Case" statements as needed. Close the code window to return to your sheet. Make a selection in B1.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "A"
            Target.Offset(, 3) = "yellow"
        Case "B"
            Target.Offset(, 3) = "green"
    End Select
End Sub

 

[JoeMo]

In D1:

=IF(B1="","",LOOKUP(B1,{"A","B","C","D","E","F","G"},{"color1","color2","color3","color4","color5","color6","color7"}))

substitute your preferred colors for "color1","color2", ....

 

[craigwojo]

Thank you both so much,
God bless,
Craig

 

[craigwojo]

JoeMo,

When I make a validation list for "B1" it doesn't correspond to the answer in cell "D1"
I needed to change the validation list to the following list names...
TAGS - Metal Detectable Hardback
TAGS - Non-MD Hardback
TAGS - VINYL
PLACARDS - Metal Detectable Hardback
PLACARDS - Non-MD Hardback
PLACARDS - VINYL
OTHER

and the answers would be 1.00, 2.00, 3.00, 4.00, 5.00, 6.00, 7.00

 

[JoeMo]

JoeMo,

When I make a validation list for "B1" it doesn't correspond to the answer in cell "D1"
I needed to change the validation list to the following list names...
TAGS - Metal Detectable Hardback
TAGS - Non-MD Hardback
TAGS - VINYL
PLACARDS - Metal Detectable Hardback
PLACARDS - Non-MD Hardback
PLACARDS - VINYL
OTHER

and the answers would be 1.00, 2.00, 3.00, 4.00, 5.00, 6.00, 7.00

Why didn't you give us that information with your OP? Would save us all some time. Post the version of the formula I gave you that you have placed in D1.

 

[Fluff]

Another option would be to create a lookup table, then you can use a Vlookup to get the value you want.

 

[craigwojo]

Thank you, everyone... just getting trying to set this up and cannot find a way to explain. Anyways... I will research it and learn. Thank you for trying and answer though. Have a great day.

 

[Fluff]

If you create a list like

Book1
	A	B
1	Text	Values
2	TAGS - Metal Detectable Hardback	1.00
3	TAGS - Non-MD Hardback	2.00
4	TAGS - VINYL	3.00
5	PLACARDS - Metal Detectable Hardback	4.00
6	PLACARDS - Non-MD Hardback	5.00
7	PLACARDS - VINYL	6.00
8	OTHER	7.00
List

You can then use this formula in D1
=VLOOKUP(B1,List!A2:B8,2,0)
change sheet name in red along with the range, to suit