Match 2 columns and display options in the third column that matches the 2 columns

**SBBmaster09** · 04-23-2013, 12:41 AM

Can I have a help,

I am making a tracker in excel and this drives me crazy. I have 3 list and need some codings in data validation, This is a sample but this is not the main tracker. Options is more than two always.

LIST 1 LIST 2 LIST 3
Red Male Jon
Red Male Renmai
Red Female Ghie
Red Male Jeff
Red Female Sarah
Orange Male Madjo
Orange Female Darl
Orange Male Rex
Orange Female Jenny
Orange Male Jessie

I have a tracker fields, the Team Color, Gender and Name. If I choose Red, the Gender column should list options which is under red, male and female, and if i chose male, the Name column should list options all the red and male matches which is Jon, Renmai and Jeff, If i choose female it will list Ghie and Sarah. This is a simplier but in actual there are many options so do not depend on this. I need the codes inside data validation using name range, i am using OFFSET, MATCH, INDEX, COUNTIF, but i cant do the third column. It needs the Column A and Column B should match.

Help please. Thanks.

**:) Sixthsense :)** · 04-23-2013, 01:04 AM

Refer the attached file for details

**SBBmaster09** · 04-23-2013, 01:27 AM

Thanks. but how about if Red and Orange has 3 or more options, here is a sample of the code ive been using in the 2nd column it works on my tracker. my problem is the 3rd column.. Ive tried your code but it didnt effect. what you think? Thanks for the help.

**:) Sixthsense :)** · 04-23-2013, 01:35 AM

Please refer the named ranges I created in Post #2 file.

**SBBmaster09** · 04-23-2013, 01:51 AM

Yes I used it, but the third column didnt show anything. Please see the 2nd file its edited.. Ive used yours, and it didnt show any dropdown. Thanks.

**:) Sixthsense :)** · 04-23-2013, 01:53 AM

Press Ctrl+F3 in the file I have attached and have a look in the Named Ranges.

**SBBmaster09** · 04-23-2013, 03:16 AM

Ei I see the problem, what if I named the ranges in my own, there is difference in name there is space so I cannot name the range with space.. is there other option, I could not also remove the space on my data because it will be broken.. Thanks

**:) Sixthsense :)** · 04-23-2013, 03:22 AM

You just name it by replacing the spaces with underscores. When redirecting the data to named range use substitute to convert the spaces to underscores so that it will get redirected to named ranges.

**SBBmaster09** · 04-23-2013, 03:30 AM

=LEFT(H4,3)&"_"&LEFT(I4,4)

i used this but how can i substitute the space with "_"

Example: AD_Task 1

**:) Sixthsense :)** · 04-23-2013, 03:37 AM

SUBSTITUTE(text, old_text, new_text)

Something like this...

Formula:

Please Login or Register  to view this content.

**SBBmaster09** · 04-23-2013, 03:39 AM

i used =LEFT(H4,2)&"_"&(SUBSTITUTE(I4," ","_")) in naming the ranges and now in data validation for column 3 i used =INDIRECT(LEFT(H4,2)&"_"&(SUBSTITUTE(I4," ","_"))) but how come it doesnt display anything??

**:) Sixthsense :)** · 04-23-2013, 03:42 AM

Please attach your workbook

**SBBmaster09** · 04-23-2013, 03:48 AM

This is my actual tracker thanks really for the help.

**SBBmaster09** · 04-23-2013, 03:50 AM

I got it sorry its ok nowThanks for the help.. Super Thanks.

**SBBmaster09** · 04-23-2013, 04:01 AM

Last problem to make =LEFT(H122,2)&"_"&(SUBSTITUTE(I122," ","_"))

in my formula only space will be substituted, how can I substitute 2 old_
text??

**:) Sixthsense :)** · 04-23-2013, 04:02 AM

Glad you fixed it and thanks for the feedback

Please have a look in the below link for more details:-
http://www.contextures.com/xlDataVal01.html

**:) Sixthsense :)** · 04-23-2013, 04:03 AM

Originally Posted by SBBmaster09

in my formula only space will be substituted, how can I substitute 2 old_
text??

Sorry I am unable to understand

**SBBmaster09** · 04-23-2013, 06:40 AM

Originally Posted by :) Sixthsense :)

Glad you fixed it and thanks for the feedback

Please have a look in the below link for more details:-
http://www.contextures.com/xlDataVal01.html

Thanks a lot for the help. It helps me more. Thanks.

**:) Sixthsense :)** · 04-23-2013, 06:51 AM

Glad it helps you and thanks for the feedback

Match 2 columns and display options in the third column that matches the 2 columns

LinkBack

Thread Tools

Rate This Thread

Display

Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Re: Match 2 columns and display options in the third column that matches the 2 columns

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions