Data validation

[lecet444]

OK, this is driving me crazy lol
I cant figure out a way to for example
column 1 has like 3 values, and column 2 has 3
if 1 value from column it will call up a value from column, and vice versa.
what I want to do is
part number and description in another column, so if you were to select part number the description would come up for it, or if you select description part number comes up.
I've been toying with vlookup but nothing.
any input would be appreciated

 

[jeffreybrown]

Hello & Welcome to the Board,

Are you saying if you select something from A1 you would like B1 to show only those choices that match or if you select from B1 you would like A1 to show those choices.

This is backwards from what I have ever seen, it is always one way, select A1 and B1 is drilled down because B1 data validation uses the Indirect function to give you unique values.

If this is the setup you want then some VBA would be required.

If not, check out this.

http://www.contextures.com/xldataval13.html

or

http://www.contextures.com/xldataval15.html

 

[Smitty]

Welcome to the Board!

You need to use VBA. I've done it in the past with a check box that allows the user to select which option they want to select. Here's an example:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox1_Click()<br>    <SPAN style="color:#007F00">'   Enter $ Down Payment</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> CheckBox1.Value = <SPAN style="color:#00007F">True</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Range("I4")<br>            .Interior.ColorIndex = 0<br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                .Formula = "=H4/G4"<br>                    <SPAN style="color:#00007F">With</SPAN> .Offset(, -1)<br>                        .Interior.ColorIndex = 24<br>                        .Value = ""<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                [H4].Select<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        CheckBox1.Caption = "Enter % Down Payment"<br>    <SPAN style="color:#007F00">'   Enter % Down Payment</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Range("H4")<br>            .Interior.ColorIndex = 0<br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                .Formula = "=G4*I4"<br>                    <SPAN style="color:#00007F">With</SPAN> .Offset(, 1)<br>                        .Interior.ColorIndex = 24<br>                        .Value = ""<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                [I4].Select<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        CheckBox1.Caption = "Enter $ Down Payment"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[lecet444]

Thank You for the quick replies, it looks like I`m going to be on this forum quiet often.
Ok here is whatI want. I'm very new to VBA, but I'm starting to learn, I just have to kind of do this for work.
for example
column 1 (done as data validation list) and column 2 (also data validation list)

A​

B
C
D
E
each line has the ablility to choose from (A-E)
with vlookup, A=1, B=2, and so on.

Now for work there are 2 ways a part is named, some ppl dont know both, and some only know 1
sooo,
I want 2 columns beside eachother, if someone were to pick the number 1, A would show on the first column, or if someone were to pick A, 1 would show on the second column.

Thank You in advance

 

[Smitty]

Why not use two validation lists, one for naming convention #1 and the other for #2? You can put a header above each to give users clues as to which to use.

Then use an If statement in your VLOOKUP(s) to reference whichever was selected. You might need to use a helper column, but it'll be easier than coming up with the VBA if you're not familiar with it.

Or come up with a separate list everyone can understand and add that to your parts list.

 

[lecet444]

I appologize I`m kind of new at this
1. make data validation on cell eg A1 (for first list)
2. make data validation on cell A2 (for second list)
3. how can i incorporate vlook up in any of those cells. I must have spent over 3 hours at work trying different things, if you can really break it down it would be greatly appreciated.

 

[Smitty]

OK, this probably isn't the most efficient approach, but it's a relatively simple example:


<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Part Name</td><td style=";">Part No</td><td style=";">Data</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Widget</td><td style="text-align: right;;"></td><td style=";">yada</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Part Name</td><td style=";">Part No</td><td style=";">Data1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Widget</td><td style="text-align: right;;">1234</td><td style=";">yada</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">COUNTA(<font color="Red">A2:B2</font>)=0,"",IF(<font color="Red">COUNTA(<font color="Green">A2:B2</font>)=2,"Please enter one value or the other",IF(<font color="Green">B2="",VLOOKUP(<font color="Purple">A2,A5:D5,3,FALSE</font>),IF(<font color="Purple">A2="",VLOOKUP(<font color="Teal">B2,B5:C5,2,FALSE</font>),""</font>)</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

If you want to post an example of your validation sheet and the data sheet someone can come up with some code for you. See the link in my sig for how to do it.

 

[lecet444]

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>part name</TD><TD>part no</TD><TD>data</TD><TD style="TEXT-ALIGN: right"></TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD>B</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>B</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>C</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>D</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>E</TD><TD style="TEXT-ALIGN: right">5</TD></TR></TBODY></TABLE>

Sheet1​

here is exactly what I want. 2 data validation columns, if i were to pick one or the other, the latter would change to the corresponding value.
ie. if i were to choose part name B, part number 2 would come up and vice versa

 

[lecet444]

If there is vba code i could use it too

 

[Smitty]

My example above assumes data validation in A2 & B2 based on separate lists. Then use a formula to determine which list has been selected and VLOOKUP based on the selection.

My example reference table is below the list selections.