If/then

craigwojo

Board Regular
Joined
Jan 7, 2005
Messages
188
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,543
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.
Code:
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,669
In D1:
Code:
=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

Board Regular
Joined
Jan 7, 2005
Messages
188
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

MrExcel MVP
Joined
May 26, 2009
Messages
16,669
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
Another option would be to create a lookup table, then you can use a Vlookup to get the value you want.
 

craigwojo

Board Regular
Joined
Jan 7, 2005
Messages
188
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,440
Office Version
365
Platform
Windows
If you create a list like

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Text</td><td style=";">Values</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">TAGS - Metal Detectable Hardback</td><td style="text-align: right;;">1.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">TAGS - Non-MD Hardback</td><td style="text-align: right;;">2.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">TAGS - VINYL</td><td style="text-align: right;;">3.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">PLACARDS - Metal Detectable Hardback</td><td style="text-align: right;;">4.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">PLACARDS - Non-MD Hardback</td><td style="text-align: right;;">5.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">PLACARDS - VINYL</td><td style="text-align: right;;">6.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">OTHER</td><td style="text-align: right;;">7.00</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">List</p><br /><br />

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
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top