• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dependent drop down combo box. (see attached file)

Hi Guity ,


Can you check out this workbook ?


https://docs.google.com/open?id=0B0KMpuzr3MTVOXgzY3N4clZRVkE


I have done the dependencies only for :


Country = United States


Within this country , there are 4 cities , and within the 4 cities there are 20 individuals.


I did not extend this to more countries , cities and individuals because it is just more of the same ! There is no code involved.


Narayan
 
Naryank,

Can I do the same thing with Combo box? Is combo box better than a drop down menu? What is the difference?


I will get back to you on this very soon.

You made my day. I have a project that I am working on.

Guity
 
Narayan,

Should I have the offset function for defined name ranges?


My first combo box has 52 entries and the second and third combo boxes have 60 entries each. Should I do some calculation for each of the entries? and they may change from week to week?


Can you explain it a little bit more?


Thank you very much for your help. I couldn't wait to go through this the next day so I came back to see what you have done....


Kindly,

Guity
 
Micro loop hair micro loop ring hair extensions extension is the newest innovation in hair extension technology. These extension strands are attached to the natural hair via micro loop hair extension that secure each strand. Micro hair extension strands are attached at about a distance of 0.4 inches from the scalp.


With micro loop extensions, there is no need for glue or any adhesives to secure the strands which prevents scalp irritation. Also, since no adhesives are used there is no need to iron the extensions thus maintaining the integrity of the natural hair is preserved.


Most of the time, attaching micro loop hair extensions are left to the stylists. Others though prefer to do it themselves but doing so may result in uneven or crooked rows of hair extensions.


Stylist ensure that extension strands are attached via straight sections. Section by section stylists attach the hair using straight rows achieved micro ring loop hair extensions from careful combing. As the stylist takes a small section of the natural hair, twists it, and places the section through the white plastic loop of the loop strand of the micro hair extension. The stylist then pulls the plastic loop tab until the natural hair is threaded and free from the micro bead. Afterwards, the extension hair strands are pulled towards the scalp while clamping down the beads with a pair of pliers. The stylist then moves to another remy loop hair extensions section and carefully attaches each row making sure everything is aligned and even.


Micro loop extension hair requires the same care as that of natural hair. They should be shampooed and conditioned adequately. Most hair extensions come from natural human hair and if not conditioned properly, have the tendency to become dry and matted. Just like normal hair, they must be combed, cleaned regularly and moisturized. Usually stylists require micro ring hair extensions for sale hair to be at least 3 to 4 inches before micro loop extensions can be attached.
 
wigs hair extensions


wigs could be classified into short, half wig cheap hair extensions extension and long. From the style the wigs might be classified into Straight,Curly and Wavy. From the material the wigs might be classified into human hair wigs,Synthetic wigs, European wigs and African American wigs.

Lace wigs are specially created for all those with moderate to serious hair loss, but may be worn by any individual who desires the good quality lace affords. This lace produces the illusion that the hair really grows from the scalp. It truly is made of a fine poly-silk mesh base in which fibers are hand tied to this thin breathable fabric. clip in hair extensions It truly is placed at either the hairline or from the front towards the crown, which can be parted in any direction for maximum styling versatility. They may be light-weight and softer to the touch than regular wigs.

The cheap ray bans sunglasses for every occasion.cheap ray bans If you are a professional and pondering upon upgrading your personalitys eloquence in your office or if you are the regular party animal Ray bans huge collection is the only destination you should be heading for.ray ban sunglasses uk Ray Bans collection also boasts of a wide variety of sunglasses each for men, women and kids.

Nike brand name is one of the most well-known footwear in the globe. clip in human hair extensions new nike air max uk Sneakers are properly known for their longevity, comfort and ease and vast array of choices. Nike shoes are well-known for their quality and delivering greatest functionality.For occasion, which is substantially beautiful, enchanting and interesting footwear for all the entire world wide sportsmen.cheap nike air max 2012 They not only give you a sheer piece of control in your life but human hair extensions also give you a huge mental relaxation. The footwear are utilized for informal wear and sportswear.
 
Hi Guity ,


The master list of countries , cities and individudals is in columns H , I and J , on Sheet1.


First we need to connect the countries and the cities ; for this , the name of the country to which the cities belong , will be repeated as many times as there are cities e.g. if the U.S. has 4 cities , then the name United States is repeated 4 times. This connection is done in columns A and B , on Sheet1.


Next we need to connect the cities and the individuals ; this is done in columns O and P. Following the same concept as in the case of the countries and the cities , each city name will be repeated as many times as there are individuals from that city. For the same example of the U.S. , for the 4 cities , since there are 6 individuals from Washington D.C. , Washington D.C. should be repeated 6 times.


Thus Sheet1 just organizes the data in the way that makes it easy to create the dependent comboboxes' lists.


Generating these is done using the formulae on the Calculations tab , and two named ranges :


1. Selected_cities , which refers to the formula :


=OFFSET(Sheet1!$B$2:$B$33,Calculations!$B$2-1,0,Calculations!$B$3,1)


2. Selected_individuals , which refers to the formula :


=OFFSET(Sheet1!$P$2:$P$21,Calculations!$B$6-1,0,Calculations!$B$7,1)


If you need to extend the 3 ranges viz. the countries , cities and individuals , all you need to do are :


1. Extend the master list of countries , cities and individuals , in columns H , I and J , as required.


2. Make the necessary connections between the countries and the cities , in columns A and B , on tab Sheet1.


3. Make the necessary connections between the cities and the individuals , in columns O and P , on tab Sheet1.


4. Change the range address references $B$33 and $P$21 in the above formulae.


5. Change the range address references on tab Calculations viz.


a) $H$10 in the formula in B1

b) $A$33 in the formulae in B2 and B3

c) $B$33 in the formula in B5

d) $O$21 in the formulae in B6 and B7


Narayan
 
Narayan,

I stayed up last night and I thought a lot about what you have posted. I mostly understood what you have done. But I don't know if it works in my case.


I have 3 categories: 1-State 2-contract number for each state 3- city

I have these categories for 2011 and 2012

My range for 2011 is static

My range for 2012 is going bigger and bigger every week.


I will test this and I get back to you with questions. Thank all for your time and effort.No words to express my appreciations....


Guity
 
Hello Naryan,


I have been working on this project to add it to my own project:


I am experiencing these issues:


1- If I select a state that has 4 cities, and these 4 cities are duplicated in 176 rows. The second drop down menu shows 176 rows. What should I do to get only 4 cities in the second drop down menu?


2- In this function. OFFSET(Sheet1!$B$2:$B$33,Calculations!$B$2-1,0,Calculations!$B$3,1)


Why do you deduct 1 from Calculations!$B$2?


3- INDEX(Sheet1!$B$2:$B$33,B2+Sheet2!$H$4-1)

why do you add B2 and then deduct 1 from $H$4?


4-The drop down menu returns value and I need combo boxes to return a text, then I will use this returned text in SumIFs function. If so, then all the calculation will be changed in Calculation sheet. Any tips to overcome this?


Thank your for your time and your help, all the best to you. Guity
 
Hi Guity ,


The two tables ( one in columns A and B , the other in columns O and P , both referring to the uploaded workbook ) , are the connecting lists ; the master lists , where each country , city and individual has only one entry against each , are in columns H , I and J.


In the first connecting table , where the countries and cities are related , the country name will occur as many times as there are cities in the country ; this connecting table is not used to derive the drop-down menu for the countries ; the drop-down menu for the countries directly uses the master list of countries.


The first connecting table is used to access the correct list of cities ( depending on which country has been selected from the country drop-down menu ).


The above principle is carried forward to the connecting table between cities and individuals ; here the cities are repeated as many times as there are individuals from a city. This connecting table is used to access the correct list of individuals ( depending on which city has been selected from the city drop-down menu ).


The returned text from the country and city drop-downs is available on the Calculations tab ; following the formula used in these , you can get the selected Individual's name also.


Narayan
 
Naryan

This is my data in column A and column B


In column A I have state and in column B I have Cities


My data is like this:


State City

AL X

AL X

AL X

AL Y

AL Z


1- I pull the countries from column H for the first drop down menu that I have only one unique value for each state


1-When I link the first combo box to second combo box, I can see that the second combo box shows 3 x Cities for AL.


2-Also some of the states have common cities


You have also noted:


The returned text from the country and city drop-downs is available on the Calculations tab ; following the formula used in these , you can get the selected Individual's name also.


The drop down menu returns value in the linked cell. I need to use combo boxes that the linked cell returns a text, then I use this text in SumIFs function. The calculation tab returns text, I do agree with it. But I prefer to use the text returned by combo box in SumIf function.


Your time and your concern is greatly appreciated.

Guity
 
Hi Guity ,


In the first connecting table , the one which connects the cities to the country , please note that only the country names can repeat ; the city names need not repeat , or rather should not repeat ; the drop-down menu for the cities will show the city names from this connecting table ; so if the city names repeat , the drop-down menu will also repeat the same name. Ensure that in the country-city connecting table , the cities do not repeat.


There is no problem if different countries have identical cities ; a city name can appear under one country , and then under another country in a different row in the connecting table.


If you want your comboboxes to return text values , then replace the present Form Control comboboxes with ActiveX comboboxes. Can you try this out ?


Narayan
 
Hello Narayan,


I hope you are doing fine.


1-Ensure that in the country-city connecting table , the cities do not repeat.


I have these two columns

AL X

AL X

AL X

AL Y

AL Z

What should I do to clear my table to have two columns as:


AL X

AL Z


I mean to remove the duplicates for the cities while we keep the name of the countries as far as each country is mapped to a unique city.


2-If you want your comboboxes to return text values , then replace the present Form Control comboboxes with ActiveX comboboxes. Can you try this out ?


I can do this. But what do yo put in thes function for H4 and D4 that takes their value from drop down menu?


INDEX(Sheet1!$H$2:$H$10,Sheet2!$D$4)


INDEX(Sheet1!$B$2:$B$33,B2+Sheet2!$H$4-1)


Big thanks for your help.

Guity
 
Hi Guity ,


I saw your questions just now. It's late here , and I think I can work on this tomorrow morning.


I hope it's OK with you.


Narayan
 
Narayan,

You did a lot for me. I am very grateful to you. I worked on this all my weekend and I am tired too.I will wait for your solution, thank you very much.


On the previous email, I said what we can use in index function if the value of linked cell to be a text, I think, instead of index function, we can use an equal sign to get the value of linked cell of the combo box.


Also, I thought to remove the duplicates for column a, we can concatenate column a and b and then remove duplicates and then separate the text with left and right function.


By end of year I have 70000 to 80000 rows and I think I should look for a shorter way that takes less manipulation and consequently less errors.


You have a good night.


Guity
 
Hi Guity ,


Can you check out the uploaded workbook ?


https://docs.google.com/open?id=0B0KMpuzr3MTVT3JVSmVVd2ZtNlk


There are 4 tabs in the workbook viz. Data , Combo-Boxes , Calculations and Duplicates.


The Master lists , and the connecting tables are on the Data tab.


The connecting tables with duplicates , can be put in the Duplicates tab ; the Country City connecting table is to be populated in A2:B100 or as far as you want. Similarly , the City Individual connecting table is to be populated in O2:p100 or as far as you want.


Once this has been done , click on the two buttons in the Duplicates tab ; these will populate the final connecting tables on the Data tab , after removing the duplicates.


Now , the combo boxes can be used.


Let me know if any additions / changes / corrections are required.


Narayan
 
Hello Narayan,

I came to work and the first thing I did was to see if I have a response from you.Prior to that I checked the site at home. I am very, very glad that I have the response from you, I will get back to you.

Many thanks for your help,

Guity
 
Narayan,


Does this code work for each number of rows? can we take care of this task without code?


Concatenate column A and B, remove duplicates and then with left and right function to separate them?


Many thanks,


Guity
 
Hi Guity ,


You can take care of the duplicates using either formulae or VBA. I have used VBA because the table setup is a one time task , unless you have a requirement by which this will keep getting modified frequently. If that is so , then probably formulae are better , since you do not have to remember to run the macro by clicking the buttons ; the formulae will keep the tables up-to-date all the time.


You decide.


As of now , I have not used named ranges to take care of expanding data ; this can always be added at a later stage. Right now , you need to decide whether the concept is acceptable. Once this is agreed upon , then we can take it forward.


Try the three combo boxes and see whether the results on the Calculations tab are in the format you want. See whether the combo boxes themselves are reliable.


Narayan
 
Hello Narayan,


yes I do agree with the concept, I inserted combo boxes and connected the first table to second table. I remove the duplicates by functions and the second combo box shows only the cities related to that state.


Now I need to connect the second to third. I need to remove duplicates for cities, but the point is that my individul column has characters between 1 to 3. If I want to concatenate and then remove the duplicates and use right and left function to separte the text, I can't. because the individual columns has from 1 to 3 characters spread in 10000 rows.


I need a function that removes the duplicated for column o and p.


Many thanks,
 
Hello Narayan,

Narayan,

I try to build it and send it to you. I exactly follow your steps and even the first combo box doesn't work now . My combo box doesn't show more than 4 entries, while it should show 11 entries. Any feature that I can change on the combo box properties?


Thank you,

guity
 
Hi Guity ,


I have faced similar problems with the ActiveX combo boxes , where the scrollbar is missing , with the result the remaining items are not displayed. I don't know whether this problem can be solved , since it does not happen all the time. Of course , I never faced this problem with the first combo box ; sometimes it happened with the second and the third combo boxes.


I did not face such problems with the Form Control combo boxes.


Anyway , if you can build your sample workbook and either upload your file or send it to me by email , we can see how to resolve these problems.


Narayan
 
Narayan,

Please give me your email address. I have one email address with your name and last name Rao., but I am not sure if this is your email address. Thanks guity
 
Narayn,

One discovery, instead of range in defined name range, we need to put a cell number.

Right now my entries are correct for my combo box.

Regards,

Guity
 
Hi Guity ,


My email address is narayank1026@gmail.com


We can afford to put a cell reference only for the first combo box ; for the other two , which are dependent combo boxes , we cannot give fixed cell references ; we will have to use formulae to derive the list of items which need to be displayed ; hence the named ranges.


Narayan
 
You must log in or register to reply here.
Back
Top