Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Synchronized drop downs (Excel 2003)

    I have a spreadsheet that has synchronized drop downs. They work great. I am supposed to help somebody make changes. I have looked at various very good posts that show how to synchronize drop downs. However, the file I have has no macros, or code on a sheet, yet the synchronization works.
    I am attaching a stripped down version of the file.
    Thanks for any help!

    itconc

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Synchronized drop downs (Excel 2003)

    The list boxes work by using Data Validation. Select one of the cells in column A and then select Validation from the Data menu. That should show you how that works. The synchronization works by again using Data Validation in the other columns with the INDIRECT and VLOOKUP functions. Select a cell in column B and again select Validation from the Data menu to see this.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Synchronized drop downs (Excel 2003)

    Thank you - never occured to me to look there.
    I really apreciate your help!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Synchronized drop downs (Excel 2003)

    The dropdowns use the Data | Validation feature in combination with lots of named ranges and the INDIRECT function. It's probably best explained using an example.

    Cell A8 on the Input Sheet has Data | Validation set to allow selection from a list, its source is the fixed range T87:T95.
    Let's say you select Completion Efficiency from the dropdown list.
    Cell B8 has Data | Validation set to allow selection from a list too, its source is a formula
    <code>
    =INDIRECT(VLOOKUP($A8,LookupTable1,2,0))
    </code>
    LookupTable1 is a named range created in Insert | Name | Define, it refers to ='InPut Sheet'!$T$87:$X$95.

    <code>VLOOKUP($A8,LookupTable1,2,0)</code> looks up the value of A8 (Completion Efficiency) in the first column of LookupTable1 (column T) and returns the corresponding value from the second column (column U). In this example, this is "Subproblem2". So the INDIRECT function returns the range named Subproblem2. This refers to ='InPut Sheet'!$U$165:$U$175. In other words, the range U165:U175 is the source range for the dropdown in cell B8.

    The other dropdowns work similarly.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Synchronized drop downs (Excel 2003)

    Thanks Hans. I appreciate the explanation.
    itconc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •