Sorted and unique validation list

[YasserKhalil]

Hello everyone
I have two columns one for Items and the other column for types
In sheet2 I need in cell C4 a data validation list (sorted and unique) for the items in sheet1.range("C6:C41")
And after selecting specific item to have appropriate data validation list in D4
For example if I select the item B from cell C4 then in D4 I need to have a validation list of (B1 - B2 - ...... B15)
I appreciate any help
Thanks advacned for help

[Winon]

Hello YasserKhalil,

I know that there are many members on this forum who are against the phrase "See the attached sample workbook"

I am not inclined to enter into lengthy explanations of how I have thrown something together for you to have a look at, and try as and when you have time.

So yes, here it comes!!! *** Try the attached Workbook ***

Regards.

[jewelsharma]

this should do the dropdowns (but does not do any sorting).

[YasserKhalil]

Thank you very much for both of you
It worked like charm. Thanks a lot foe this great help
Kind Regards

[YasserKhalil]

Sorry for disturbing you again..
Can it be done using formulas and named ranges ?

[Winon]

Hello YasserKhalil,

Can it be done using formulas and named ranges ?

Does this mean you would want it without any VBA code?

If that is so, it could turn out to be very difficult if at all possible.

Cant you make do with this revised sample Workbook which contains some VBA?

[YasserKhalil]

Thanks a lot Mr. Winon
In fact I need to use formulas in my case as I would intend to create validation list in range(C4:C50) and I need formulas to be more flexible in using them
Another point I need to get rid of blanks in these lists ..and if possible to be sorted
Thanks a lot for great help

[Winon]

Hi YasserKhalil,

...In fact I need to use formulas in my case as I would intend to create validation list in range(C4:C50) and I need formulas to be more flexible in using them
Another point I need to get rid of blanks in these lists ..and if possible to be sorted

Ouch!, the above clarification is very different from your original requirement as per Post#1.

I will be consulting with a couple of companies on several other issues until at least Thursday - maybe until Friday.

As soon as I have time after that, I promise to look at you issue again.

Kind regards.

[YasserKhalil]

Thanks Mr. Winon for reply
In fact there is no difference .. I just needed to simplify my request and concentrate on the essence of the issue
I thought it would be easy include more cells to apply the solution on them but it seems that it is complicated ..
Regards

[Winon]

Hello YasserKhalil,

As promised, I had a look at your Posts again and it seems we are not exactly on "the same page", regarding your issue.

In fact I need to use formulas in my case as I would intend to create validation list in range(C4:C50) and I need formulas to be more flexible in using them
Another point I need to get rid of blanks in these lists ..and if possible to be sorted.

Does the above quote refer to Sheet 2, or did you actually mean Range C6 to C50 on Sheet 1?

Please could you attach a new sample workbook for clarification of your precise requirements.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Regards.

[YasserKhalil]

Thanks Mr. Winon for reply
As for the attachment the source for the lists in Sheet1
And in sheet2 I need the validation lists as solved in previous solutions ...
Just need the solution by formulas to be able to apply the lists to sheet2.range("C4:D50")
Hope it is clear now
Thanks for all great help provided

[hemesh]

As solution is already provided.
In case of using formulas see the attachment
The way your sheet is set up is perfect, sorted already both on items and type.

Your item column is needed to be sorted in that case. Sorting can also be done using formula but may require use of helper columns

find attached and check is this you are looking for

[Winon]

Mr. YasserKhalil,

Do you need each row in sheet2.range("C4:D50") to be unique?

In other words, if you selected a Dropdown in sheet2.range("C10"), will it be o.k. to clear all other Dropdowns in that Column, or do you want to still have any other previous selections in that column still to be visible?

Regards.

[YasserKhalil]

Thanks again for reply
I need to keep the previous entries ...
Kind Regards

[YasserKhalil]

As solution is already provided.
In case of using formulas see the attachment
The way your sheet is set up is perfect, sorted already both on items and type.

Your item column is needed to be sorted in that case. Sorting can also be done using formula but may require use of helper columns

find attached and check is this you are looking for

Very wonderful. Thank you very much for this perfect solution ...it is very very good
Best Regards

[hemesh]

@ Yasser! you are welcome and Thanks for the feedback and appreciation.

I am glad, I could be a part of providing something.