YTLE#75: DV Drop-Down based on DV Drop-Down

[paulobyrne0402]

Hey ExcellsFun, thanks a million for all the videos, they're brilliant. Quick question, I've created a table with a number of rows and columns of data, and I'm trying to create a dv list from it. The first colum has a list of manufacturers, but there are duplicates in it, and when I create the dv list from that column in the table, its giving me all the duplicates aswell. Is there any way to get rid of the duplicates when I create the dv list?

[ExcelIsFun]

If you wanted it to be dynamic you would have to build a complex array formula. See this video:

Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array Formula)

Otherwise, you could use Extract Unique list:

Excel Magic Trick 82: Advanced Filter Extract Unique Records

Then make your DV.

[elkim27]

Mr. ExcelIsFun, Thanks for all your videos. Learning a lot here. It seems like the data validation of data validation doesn't work if the name is dynamic. So if LP was a dynamic named range using the offset function, then the indirect of that name won't pull up anything. Would you happen to know a way around that? Thank you!

[ExcelIsFun]

try these videos:

You Tubers Love Excel #1: Dynamic Data Validation & OFFSET

Excel Magic Trick # 259: Dynamic DV List Based On DV List

[graceoverall]

I'm trying to do something similar, but I'm working in Open Office so obviously the shortcuts don't work. What are the actual command names or dialog boxes? Ok, so I have my Data Validation drop downs setup, They are parallel data ranges similar to what you have here. But what I want is for it to look at the particular value I've chosen from either drop down (so it works in both directions) and automatically selects the CORRESPONDING VALUE in the other drop down.

[ExcelIsFun]

I do not know how to use Open Office .

[poor750]

@ExcelIsFun

Does Microsoft pays you? I love all your videos.

[ExcelIsFun]

No - I don't even think Microsoft knows that excelisfun exists.

I am glad that you enjoy the videos.

[lweesian]

how could i do dv drop down base on another and another.

[ExcelIsFun]

This video shows how to do that.

[jiejenn]

I love the drop down menu but right now I am trying to do more with the drop down menu.

Is there any way that when I select an item from the drop menu, the table will only display what I select from the drop down menu?

For example, if if choose distribution + drp, = only the one that matches to the table will display on the worksheet.

[ExcelIsFun]

Yes, Try this video title:

Excel Magic Trick 185 Dynamic Formula Extract Data 1criteria

[pinkpearl80]

Hi ExcellsFun,

SORRY consider this:

I tried...This works fine. however, if I have to proceed to next row and then next and keep on changing the search criteria it should automatically reveal cell against the same e.g

CompanyPro NumberPrice

db115Price25

hdfc220Pro Number?

hsbc325

max430

citi535

[ExcelIsFun]

Dear pinkpearl80,

I do not understand your question. Can you re-state it? Or send workbook with restated question to my excelisfun gmail account?

--excelisfun

[pinkpearl80]

Hi ExcellsFun,

I tried...This works fine. however, if I have to proceed to next row and then next and keep on changing the search criteria it should automatically reveal cell against the same e.g

CompanyPro NumberPrice

db115Price25

hdfc220SKU?

hsbc325

max430

citi535

[92126CA]

Thanks for the video. Very useful.

However, one problem I ran into was when I tried to do this for a second row I got an error message. In your example the error message would have appeared after trying to to the indirect function on F3. Error message reads: "The Source currently evaluates to an error. Do you wish to continue?" Any ideas?

[ExcelIsFun]

Dear 92126CA ,

Send your workbook to:

excelisfun at gmail.

I'll take a look.

--excelisfun

[albert23rd]

hello sir,

i really like the way you teach excel... thanks sir. i will view your tutorial from the start when i have a time. your very helpful to me sir.. thank you very much...

[ExcelIsFun]

Dear albert23rd,

I am glad that they are helpful.

--excelisfun

[AmayaAus]

Exactly what i was looking for. Thanks!!

[ExcelIsFun]

Dear AmayaAus,

The most common question that I get at excelisfun is how to do DV List based on another DV list!

I am glad that it helps!

--excelisfun