[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

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
0
turgan
Asked:
turgan
  • 10
  • 8
  • 4
1 Solution
 
magedroshdyCommented:
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
0
 
turganAuthor Commented:
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.
0
 
magedroshdyCommented:
what about filtering it to another column and make that new column the source without affecting the first one?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
turganAuthor Commented:
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.
0
 
magedroshdyCommented:
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
0
 
turganAuthor Commented:
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
0
 
patrickabCommented:
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
0
 
magedroshdyCommented:
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
0
 
patrickabCommented:
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
0
 
turganAuthor Commented:
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.
0
 
patrickabCommented:
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

0
 
turganAuthor Commented:
correct.
0
 
patrickabCommented:
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




0
 
patrickabCommented:
Oops - file attached...
dropdowns-03.xls
0
 
turganAuthor Commented:
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
0
 
patrickabCommented:
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
0
 
turganAuthor Commented:
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.
0
 
patrickabCommented:
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
0
 
patrickabCommented:
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
0
 
turganAuthor Commented:
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.
0
 
patrickabCommented:
turgan,

You wrote: "so no option is a dependent of anything else."

If that is the case then why bother to have dependant dropdowns. They would be totally unnecessary.

Do you understand how the lists in the Data Validation sheet are placed there? I somehow doubt it.  Are you aware that they change according to the selections made in the dropdowns? Have you actually used the dropdowns on Sheet1? Somehow I don't think you have even used them, otherwise you couldn't tell me "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." How else do you think it works? Had you used the dropdown for Paper Type, selected Autocopy, and then used the Paper Kind dropdown you would have seen that the only options available would be only those relevant to Autocopy paper. How does that square with your ealier comment "so no option is a dependent of anything else."?

You round off your comments with "What I need surely uses this but is a much more advanced version." and that's without understanding how the macros work that I have provided and without suggesting ways to improve or achieve what you want.

Do please try the dropdowns at the very least...

Patrick
0
 
patrickabCommented:
turgan - Thanks for the grade - Patrick
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 10
  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now