Home/ Questions/Q 6385
Next
In Process
Computing Staff
  • 0
Asked: 2021-11-19T05:15:13+00:00 In:

Dependent Drop Down Menues With Numbers

  • 0

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

Share

    5 Answers

    1. 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.

      • 0
    2. 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

      • 0
    3. 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?

      • 0
    4. 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/

      • 0
    5. 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/

      • 0