Dependent drop down menues with numbers

Microsoft Office excel 2007 home & stude...
November 8, 2010 at 00:24:35
Specs: Windows 7
First greetings to all,
I am complete novice to EXCEL and in need for help. I have designed the document and struggle threw math but I don’t know how to do drop down menus that are able to, when chosen a data in them, produce referent value for calculation.
Cells D11, F11 and K11 should have the drop down menus that are displaying product options in my offer (2 x dimensions and price).
I would like when chosen any value in drop down menus (in each one), the other values that are not relevant not to be seen.
Regards

https://docs.google.com/leaf?id=0B4Q7DOB4mw0uNmZlMDM4OTEtZWIxZC00YzVlLWJkMTItYWUyZWVkNzRjZWIx&hl=en&authkey=CLT5xuUH


See More: Dependent drop down menues with numbers

Report •


#1
November 8, 2010 at 03:40:26
The process for creating dependent drop downs requires the use of the INDIRECT function and Named Ranges.

Instead of detailing all of the steps in this response, I suggest that you review the instructions found here:

http://www.contextures.com/xlDatava...

You can also Google "Dependent Dropdown List" for a lot more hits.

If you have any trouble setting these up, let us know.


Report •

#2
November 8, 2010 at 05:44:58
Thanks for your response DerbyDad03,

I have tried and it works fine with words but not with numbers. I can not name range of numbers.

I suppose there is the way to do dependent list with numbers.

I am new to Excel, and information's are overwhelming.

Regards


Report •

#3
November 8, 2010 at 06:08:49
Assuming you have figured out how to use INDIRECT to get your dependent drop downs to work, here is a response I wrote in another thread related to dependent drop downs and using numbers for a named range:

re: "I can not define a number as a name"

You don't have to. That's the beauty of using INDIRECT. It will accept any text string and use it refer to a range.

Name the "number ranges" with names like my100, my200, etc.

Then use =INDIRECT("my" & A1)

With 100 in A1, this will evaluate to =INDIRECT("my100") and refer to that range


Report •

Related Solutions

#4
November 8, 2010 at 10:01:01
Sorry DerbyDad03,

I am stupid. After I have red your post I have tried every combination that I can think of, but obviously not the right one.

I am uploading document named TEST - inside are all needed data for one line of Dependent Drop Down Menus that I need. I hope that I will be able to continue on my own after I see your work.

https://docs.google.com/leaf?id=0B4nFBFvjmJRvMDViMmI5NDEtYjA3ZC00NzVmLWJjNjYtNjZlZjU0MzY0N2Q0&hl=en&authkey=CMjA7KAL

If you have time and will, please help me to solve my problem.

Regards


Report •

#5
November 8, 2010 at 10:43:26
Unfortunately, I can't access Google docs from work. Let's try a brief example and see if that works for you:

I have a Drop Down validation list in A1 that offers the choice of 100 or 200.

I have a Drop Down validation list in B1 that is dependent on the Drop Down in A1. In other words, the list of choices in B1 will change based on the choice made in A1.

When 100 is chosen in A1, I want the Drop Down in B1 to offer Red, Blue and Green.

When 200 is chosen in A1, I want the Drop Down in B1 to offer Black, Brown, and Orange.

I have this table:

     C         D  
1   Red      Black
2   Blue     Brown
3   Green    Orange

- I select C1:C3.
- I click in the Name Box above Column A, enter my100 and hit Enter.

I have now given C1:C3 a Range Name of my100.

- I select D1:D3.
- I click in the Name Box above Column A, enter my200 and hit Enter.

I have now given D1:D3 a Range Name of my200.

I do this because, as we know, I can't use a number as a Range Name.

Now, for the Drop Down in B1, I use:

Data Validation
Allow: List
Source: =INDIRECT("my" & A1)

The INDIRECT function will concatenate the string my with the number in A1 and use that as the Source for the Drop Down in B1.

When A1 contains 100, B1's Drop Down will be populated with the values in the range named my100, which refers to C1:C3 or Red, Blue, Green.

When A1 contains 200, B1's Drop Down will be populated with the values in the range named my200, which refers to D1:D3 or Black, Brown, Orange.

Does that help?


Report •

#6
November 8, 2010 at 12:56:38
Told you I am stupid. Again, I can not name C1:C3 a Range Name of my100 (The name you entered is not valid). I am doing something conceptually wrong.

Is it possible for you to send me your EXCEL file on my Email - asubasic@gmail.com

Thank you again (such an effort from your side and I can not make it work - embarrassed)


Report •

#7
November 8, 2010 at 13:27:58
DerbyDad03, I'm had a similar problem.....using 2007

I select Formula from the ribbon
I select Define Name
In the popup window
I enter the Name: my100
I leave the default Scope as workbook
I leave the Comment filed blank
I select the range of $C$1:$C$3

When I click OK I get the error message:

The name you entered is not valid
Reasons for this can include:
The name does not begin with a letter or underscore
The name contains a space or other invalid character
The name conflicts with an Excel built-in name or the name of another object in the workbook.

There are no other objects in the work book.

Now the strange part is if I re-name the range to test100
it works.

The even stranger part is if I re-name the range to mymy100
it works.

After a few quick tests, it seems you need a minimum of four letters before the numbers.

MIKE

http://www.skeptic.com/


Report •

#8
November 8, 2010 at 13:37:38
DerbyDad03,

A second problem with using the name my100 in 2007, there is a cell location MY100

When you enter the string my100 in the box above Column A, it does a GoTo cell number MY100, it does not define the range.

ADDED:

Since 2007 uses all the one, two and three letter combination between A and XFD,
it seems you cannot use any of those combos in a Named Range,
You can use XFE and beyond.

MIKE

http://www.skeptic.com/


Report •

#9
November 8, 2010 at 16:10:00
Yes, finally success.

I have managed to make first dependent drop down menu fallowing DerbyDad03 instructions. Thank you for such unselfish help.

Also, mmcconaghy helped with solution to prolong range name. After that everything works OK.

Again, thank you for your time and effort.

Now, struggling with second dependent drop down menu, based on first one, but with no success. More help needed...

Regards


Report •

#10
November 8, 2010 at 18:13:20
Thanks for troubleshooting this Mike!

I don't have 2007 (or 2010) at work so I couldn't test it. Never saw the issue you described with 2003, but it makes perfect sense. I'll keep that in mind.

You know what? I might just stop answering questions unless the version number is included in the post or specs. There's just too many little things like this that get in the way.

A. Subasic:

Since we've already explained how to create a dependent drop down list, you are going to have to be more specific than "struggling with second dependent drop down menu, based on first one, but with no success".

If you are doing essentially the same thing with the 3rd Drop Down, the same process should work. If not, you're going to have to tell use what's not working.


Report •

#11
November 9, 2010 at 02:54:57
I have created first drop down list on sheet 2 and start in cell E10 in the first sheet

Al99.5ptsirlim
1000
1250
1500

Following dependent drop down list:

Al99.5ptdeblima1000 Al99.5ptdeblima1250 Al99.5ptdeblima1500
0.5 1.0 1.5
0.6
0.8
1.0
1.5
2.0
3.0
4.0
5.0
10.0

In the cell F10, I am using formula =INDIRECT("Al99.5ptdeblima" & E10) based on your instructions and this works fine.

Now I am trying to make second dependent drop down list in cell G10 with values

Al99.5ptcenalima1 Al99.5ptcenalima2 Al99.5ptcenalima3
424.80 448.40 525.10

My goal is to make a metal calculator for weight and price. First drop down is sheet metal width, second dependent drop down is metal thickness and third should be the price.

For chosen value 1000 in first drop down list I have options:

0.5
0.6
0.8
1.0
1.5
2.0
3.0
4.0
5.0
10.0

Price for thickens 0.5, 0.6, 0.8, 1.0, 1.5, 2.0, 3.0 is 424.80, but price for 4.0 and 5.0 are different 448.40 and for 10.0 is 525.10 - is that going to be the problem?

I was trying to use formula =INDIRECT("Al99.5ptcenalima" & F10) for cell G10 but no success

Regards


Report •

#12
November 9, 2010 at 04:09:00
If the prices for the various thicknesses are set values, why do you need a drop down?

Why not just use an IF statement?

=IF(F10 = 5.0, 448.40, IF(F10 =10.0, 525.10, 424.80))

Better yet, create a list of prices next to the list of thicknesses and use VLOOKUP to bring the price in G10. That way you won't have to change the formula when the prices change...you can just edit the price list.


Report •

#13
November 9, 2010 at 08:44:24
Fallowing your advice I have implemented VLOOKUP to bring the price and everything is functioning flawlessly.

You were right, there is no need to make second dependent drop down menu since the result value for price should not change...

I think that I will be able to finish Calculator on my own. Only problem that I can see later is few items in my list of products that have a third dimension (U - profiles).

Anyway, again, thank you for you help...

Aleksandar


Report •

Ask Question