Display automatically particular list in dropdown

[paradise2sr]

Dear Sir,

Currently,I am facing a little bit challanging work.I have a problem with regard to displaying particular list based on the input.As enclosed in attachment.In data worksheet in column C and Column D which represent list of Name of Party and Name of Job.In 'required' sheet in column C, name of party is entered manually.Now in column D in "Required" sheet,the drop down should be such that it will display only that list from Column D of "Data" Sheet that matches Column C Data from "Data" sheet with that of Column C data of "Required" Sheet which is to be entered manually.

At the moment,it has been showing all the list.I want in drop down in column D only the particular list in "Required" sheet.For e.g in required sheet.

Arbind Udhyog >>>>> in column D list should show automatically in dropdown as -

Golden Don Outer
Tinki S.S. Outer
Super Don Inner

Similary like others.

Hope this can be solved by any method.

With Best Rgds,

[[email protected]]

Hi Friend,

Please find attached Excel sheet for your solution. Please note this formula will work in same format of data only.means data sould be sorted in same format.

Regards,
Suhas

[paradise2sr]

Hi thanks for the reply.

But still there is the problem of Refreshing.When I delete column C data of Required Sheet, Column D data still does not get cleared. However in dropdown there is displaying the corresponding data.

Hope this gets solved of refreshing.

With Best Rgds,
Suresh

[micope21]

Hi

Required sheet
Cell A4

Please Login or Register  to view this content.

Copy down

data sheet D3

Please Login or Register  to view this content.

Copy down.

See the file

Regard
micope21

[paradise2sr]

Dear Sir,

Thanks for the reply.I am expecting a drop down rather than a formula.I am near to the #post 2.But there is refreshing problem which I have stated in #post 3.

With Best Rgds,
Suresh

[micope21]

Ok. How many drop down you looking? One or both column C and D? This go with formula I just done or you don't want the formula?

[paradise2sr]

Sir I want one drop down in column D only.

[micope21]

Ok

See the file!!

You can Delete any in data sheet column C!!

Required sheet Column J1 number J2 Party Name.
You see only just one word instead in data sheet got 2 or more same word.
Reason done this it will mess up too many same word in drop down.

Is this what you looking for????

Regard
micope21

[paradise2sr]

Dear Sir,

I have seen the file but it is not that what I am looking for.Kindly note the following things

2.In C3 and there after 'Name of Party' is to be written manually in Required Sheet.A sample is already attached in above file.


3.When in C3 'Name of Party' is written in Required Sheet,it should automatically show only that list from 'Data' sheet from D Column in dropdown that matches C3 of "Required" Sheet with that of C column of "data" sheet.For eg.

Required Sheet >>

C3 D3 (Corresponding C3 list should be shown in dropdown only in a single cell from Data Sheet C column as shown below)
Anand Tea Ind. Taj Tea 200g
Taj Tea 500g
Sandgiya Tea Rs.10



Data sheet >>
Column C Column D
Arbind Udhyog Golden Don Outer
Arbind Udhyog Tinki S.S. Outer
Arbind Udhyog Super Don Inner

Anand Tea Ind. Taj Tea 200g
Anand Tea Ind. Taj Tea 500g
Anand Tea Ind. Sandgiya Tea Rs.10

& so on..............

Hope this has been clarified.

With Best Rgds,
Suresh

[paradise2sr]

Hi Friend,

Please find attached Excel sheet for your solution. Please note this formula will work in same format of data only.means data sould be sorted in same format.

Regards,
Suhas

Can you further simplify the formula without using dynamic Name Range.If not possible then,an alternative method.As I am using google spreadsheet too simultaneously but this is not working over there,perhaps simple formula could do so ?

With Best Rgds,

Suresh

[micope21]

Hi

Understood!

See the file!

data sheet,
Column E: I hide them to help close the gap for drop down in Reguired column D. This is array formula.

Reguired Sheet,

Column C will come up automatic once you click drop down D say. Taj Tea 200g>> column C will come up Anand Tea Ind.

Regard
micope21

[paradise2sr]

Dear Sir@micope21

You have mentioned the formula in C3 in Required Sheet.This part is to be done manually and not by a formula.In D3 only specific list should be displayed in dropdown and not the whole list that you have done in D3.

Anyhow,you have tried your best.Kindly see the workbook attached in post #2 by [email protected]

I am just expecting the result to overcome the limitation that of Post #2 that was present by forum contributor@ [email protected]

"Please note this formula will work in same format of data only.means data should be sorted in same format."

I was waiting till now just to overcome the limitation of Post #2 imposed.Otherwise,this is thread is deemed to be solved.

Thanks both of you.

With Best Rgds,
Suresh

[[email protected]]

Hi Friend,

The solution given by me is working fine, just i want to say if you want to remove validation where column "C" , where cell value is blank then you have to use macro or if your data is only for read then you can use condition formatting to hide the drop down wherever column"C" value is blank .

Regards,
Suhas

[micope21]

Hi Friend,

The solution given by me is working fine, just i want to say if you want to remove validation where column "C" , where cell value is blank then you have to use macro or if your data is only for read then you can use condition formatting to hide the drop down wherever column"C" value is blank .

Regards,
Suhas

Use this Column C =IFERROR(INDEX(data!$C$4:$C$1000,MATCH($D3,data!$D$4:$D$1000,0)),"")

This will give you a blank instead 0

Regard
micope21