Linking drop down boxes

[Anthony J.]

Hi everyone

I am trying to link 'Combo boxes' in Excel 2007 (not using code as the guy in the other thread is). Ie In the first drop down box you choose the type of cement, then in the second drop down box, different mixes of cement can be chosen DEPENDING on your choice of cement in the first.

How do you do this? Anyone?

Thanks

[mhuston]

Try this: http://www.ozgrid.com/VBA/dependent-combobox.htm

[Anthony J.]

Hey, thanks for your reply. Unfortunately I'm not using code and don't know how, so that link isn't too helpful.

[contaminated]

U mean smth like this?

[Anthony J.]

Hey! That is exactly what I need to do, but there was no explanation on how to do it? Could you post some steps please?

A million thanks!

[ChemistB]

Walk through ContaminateWitExcel's example as you read this...
For the first combo box, Right click and choose Format Control and then the Control Tab.

Input Range = Range of master list (or name of master list if using defined names)
Cell Link = cell where you want output (outputs the number of item you choose, eg. 1 for 1st entry, 2 for 2nd. For consistancy we'll choose M17

Next go to your Define Names window (Insert>Names>Define). For every item in your 1st combo list, name a range corresponding to the options for that list. Names cannot have blank spaces.

Next, set up the DependentDropDown name (choose any name) in the Define Name Window. This will be

Please Login or Register  to view this content.

where you replace ITEM1 with the name you chose for your first item in your combobox 1. In Cont.wExcel example, that was Azerbaijan.

Next, right click on combobox 2, Format Control>Control Tab and for input range, put the name you cose for DepenedentDropDown name.

I think I got everything. Normally, I put the output of the Combobox 1 directly underneath the combobox as no one needs to see that number.

Clear as mud?

[Anthony J.]

On Format Control, I don't have the Control tab... :s

[contaminated]

ChemistB has explained very elegantly...

[contaminated]

see file please

[ChemistB]

I think that Contaminated used Forms Combo Boxes and you used an Active X Combo box. Can you switch? Can you upload an example of your workbook?

In Excel 2007, The "Define Name" options are on the Formulas Ribbon. To view Contaminated's defined names, use the "Manage Names" Icon. Does that help?

[contaminated]

Can you post your workbook?

[Anthony J.]

Here you go guys, here is a copy of my workbook.

As you can see, I am trying to get it so that whichever concrete type you choose, you can then choose the mix.

Have fun with it!

[contaminated]

Can you tell me what the difference between Light Aggregate in I3 and H16?

[Anthony J.]

There is no difference except I have spelt one with a lowercase 'A'

[G0liath02]

I don't have access to a copy of excel so can't do it for you - but this might be an easier and quicker method:

As you've named all the lists on the formula's page the easier way to do this would be using list validation.

Cell B3 is set as list validation formula is: =concrete
C3 is set as list validation formula is: =indirect(b3)

Then all you have to do is change your defined names to match the entries in the defined name 'concrete' or vice versa. These should all match.

Your drop down lists are done and you can easily copy these drop downs down by copying the cells and pasting validation.

There are plenty of examples of this just a google away if need be.

[contaminated]

Thath's ok?

[Anthony J.]

Contaminated, that is fantastic!

How did you do it?

Now, I have one last question...

Each type and mix of concrete will have an associated figure of embodied carbon. When a particular mix of concrete is chosen, I want this figure to appear in Column H. I have a feeling this isn't that hard to do, just link a cell to the dependingdropdown box no? Any ideas?

But first, really that is amazing, how did you link those boxes?!

If you are ever in England, I will buy you a beer!

[Anthony J.]

For example, if you were to choose Portland, 1:2.5:5 , in Column H row 4 the figure 0.109 should appear...

[contaminated]

Can you provide me with all data?

See file. But this one ONLY for Portland

[Anthony J.]

That is incredible. You gotta tell me how you did both of those things.

Unfortunately I don't have all the data, but i can give you data on other concrete mixes which I will have to put in at a later date.

Here is a file with 3 other types of concrete (GEN 1, GEN 3, RC35) with their mixes and embodied energy details.

[contaminated]

Just input the future data into lower table. Data must be inputted relatively to its column label.

For Examle.
If u wish to write 0,876 for light-Agregate >1 Cement: 3 Sand: 6 Aggregate just write it in fourth row of lower table of corresponding column. Because "1 Cement: 3 Sand: 6 Aggregate" is 4th record.

If all right make this thread solved