Link to home
Start Free TrialLog in
Avatar of turgan
turgan

asked on

Excel Unique Dropdown and Dependent Dropdown

I need to create a unique set of values in my dropdown list for Paper Kind from a mixed set of duplicate values in a column.

Also for the next drop down list I would like to filter the drop down values to the relevant values for the previously selected Paper Type


I dont want to use a module or a Macro for security settings reasons.

Basically I want to have an intuitive way to use the Sheet1 worksheet in attached sheet.
selection.xlsx
Avatar of magedroshdy
magedroshdy
Flag of Egypt image

in the attached file
click on any cell in the target column a drop down menu will appear filled with values from the source column, but you have to keep the values in the source column unique by using data>filter>advanced filter> "unique records only" check box

if it works with you, let me show you to do make the drop down menu
test.xls
Avatar of turgan
turgan

ASKER

hello
not sure if this is the way I want to go. The table in the source sheet can have modifications so its a data table and I wont want to filter it.
what about filtering it to another column and make that new column the source without affecting the first one?
Avatar of turgan

ASKER

yes that would work however it would need to work for all the other columns too I believe? so I would need to create a table of 4 columns with only the unique values taken from the source... however im ok with it.
good to hear that you get a walk  around, now to make a dropdown menu in the target column do the following
1- select the target column, or target cells(if you don't need the dropdown menu for the header -first cell in the column)
2- choose from the menu bar > data>validation>settings tab
3- choose "list" from "allow drop down"
4- check on "ingor blank" check box
5- check on "in-cell dropdown"  check box
6- chose the source column for the "source" field
7- click on "ok" button
Avatar of turgan

ASKER

Sorry I actually know how to create dropdowns. The attached sheet shows what I exactly need in XLS format instead of XLSX. I created the dropdowns however I need them filtered properly.
selectionxls.xls
turgan,

Please separate the data in column A in the Data Validation Sheet into Paper Type in column A and Paper Kind in column B and then upload the corrected file - thanks.

Patrick
i filtered the source using data>filter>advanced filter> "unique records only" check box to columns j,k,l,m
then you have to change the validation refer to the new columns
selectionxls.xls
turgan,

See the screenshot of Sheet1 below. You apparently want 'Paper Type' and 'Paper Kind' as separate dropdowns. If you want that then please re-organise your data so that 'Paper Type' and 'Paper Kind' are in separate columns in the Data Validation sheet - see other screenshot.

When you have done that I will be able to help you.

Patrick
MWSnap-009-2010-01-06--13-50-15.jpg
MWSnap-011-2010-01-06--13-56-34.jpg
Avatar of turgan

ASKER

Hi Patrick,
I believe I attached the correct version in the ID: 26189715. I separated Type and Kind 1n this example, please see if thats what you want.
turgan,

Let's start again because we are not understanding one another. Is this what you want on Sheet1:

1. In cell Sheet1!B2 you want a dropdown of all the 'Paper Types'

2. You then want the options in the 'Paper Kind' dropdown to be determined by the selection you have made in the 'Paper Types' dropdown.

3. You then want the options in the 'Paper Weight' dropdown to be determined by the selection you have made in the 'Paper Kind' dropdown.

4. You then want the options in the 'Paper Width dropdown to be determined by the selection you have made in the 'Paper Weight dropdown.

5. You then want the options in the 'Paper Length' dropdown to be determined by the selection you have made in the 'Paper Width' dropdown.

Is that what you are wanting? Please forget how it is going to be achieved, all I need to understand is what you want.

Patrick

Avatar of turgan

ASKER

correct.
turgan,

Thanks for that most brief confirmation. As I now understand what you are wanting, I now need you for a moment to completely forget about dropdowns (that's my task) and instead concentrate on the raw data.

The raw data now needs to be re-organised by you. Again please do not refer me to one of your previous files. What I need from you is the data completed in the attached file. When you have done that I can and will provide you with a series of dependant dropdowns - so please don't waste your time on that part of the question.

On the 'Data Validation' sheet please fill in all the green cells in column B - 'Paper Type'. When you have done that, upload your file - in .xls format as I have Excel2002.
Thanks
Patrick




Oops - file attached...
dropdowns-03.xls
Avatar of turgan

ASKER

Patrick,

Glossy Type paper comes in 2 kinds. However I absolutely want the ability to expand these kinds independent from this table. That is why I found it necessary to seperate the 2 tables.

I believe normalized version of this table should be a bit different, however I would at least prefer to seperate the kind and type at this stage.

Yet, Im attaching what you had asked below.
Copy-of-dropdowns-03.xls
turgan,

Thanks for that. Unfortunately the list is not complete.

You say - "However I absolutely want the ability to expand these kinds independent from this table. That is why I found it necessary to seperate the 2 tables."

That statement has got me totally confused. The reason is that if the Paper Type and Paper Kind are on separate tables and are not linked then you could for example have Matte, Regular or Gloss - Paper Kinds for let's say BRISTOL& KARTON Paper Type. If those are not possible combinations then there is no option but to list all the possible options in the main table - 'Data Validation'!A:E. And if they are not listed then it is not possible to have dependant dropdowns.

Please come back to me on the points I have made before I start any work on this project.

Patrick
Avatar of turgan

ASKER

I see, so for now all possible combinations are listen in the main table.


but, correct, you cannot have Matte, Regular or Gloss - Paper Kinds for BRISTOL& KARTON .
but back to the 2 tables in the first example.
one was for Paper Type and Paper Kind.
and the other was for Paper type, Paper Size width & Length and Paper weight.
Bristol and Carton paper does not have Kinds attached to it in the second table Paper Type - Paper Kind table there it cannot have gloss, matte or regular as kinds.
I'm not sure if I'm making sense but I believe database wise the Paper Type & Kind should be in seperate tables. Maybe even Paper Kind and Paper Size should be in seperate tables... however if you say it's needed for dependent dropdowns the latest version should have the complete table.
thank you.
turgan,

Consider it to be a list of all possible production options. If you can have any Paper Type with any Paper Kind with any Paper Weight with any Paper Size with any Paper Width with any Paper Length then you don't need dependant dropdowns. So if for example there are these options in each category:

Paper Type  - 20
Paper Kind  - 4
Paper Weight - 15
Paper Size - 6
Paper Width  - 4
Paper Length - 4

then by having each of those option in separate tables you can have:

20 x 2 x 15 x 6 x 4 x 4 =  57600 options, which is clearly ridiculous as the number of production options. However if you have a list as you first filled out with blanks for some of the Paper Kinds then what is the dependant dropdown going to depend on  - a blank? I don't think that would work too well either!

Let's take the example you specifed "Bristol and Carton paper does not have Kinds attached to it in the second table Paper Type - Paper Kind table there it cannot have gloss, matte or regular as kinds." How does the computer know that if you don't list what is permitted? You might know it but that is of no value when the macro is running. It needs to be specified in the raw data.

I personally cannot see the reason for your reluctance to list all the options. Anyhow it's up to you. If you are able to complete the whole list then we can take the next step. I will await the file from you.

Patrick
turgan,

In the attached file on Sheet1 you will find the dropdowns for each factor. Let me know how you get on with it.

Patrick
dropdowns-03.xls
Avatar of turgan

ASKER

Patrick let me explain through the "Data Validation" list  some examples of Paper Type, Paper Kind, Size Width, Size Length, Grams interactions of paper.

Paper Kind is a dependent of Paper Type
Paper Size Width and Length is a dependent of Paper Type
Grams is a dependent of Paper Type

so no option is a dependent of anything else.

GLOSSY paper, can have either MATTE, GLOSS Paper kinds as per the table. Ignore Blank values for glossy they are in error, only for glossy type though. .. So IF I select glossy, I want only these MATTE or GLOSS paper kind options to be available in the dropdown.

If I select GLOSSY paper, I also want only 50cm, 57cm, 64cm, and 70cm to be available in width because only these are in the Data Validation Table.

If I select Autocopy paper. This has Paper Kinds of Blue End, Blue Middle, Blue First... and on I want only these to be available. and ONLY if I select autocopy paper.

The sheet you sent attached has the core functionality of creating dropdowns with Data Validation lists in excel using named ranges as sources. What I need surely uses this but is a much more advanced version.
ASKER CERTIFIED SOLUTION
Avatar of patrickab
patrickab
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
turgan - Thanks for the grade - Patrick