Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2009
    Location
    The Frozen Tundra
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello:

    I am using Excel 2007 to track some inventory items. I have a control sheet with lists for "buildings", "rooms", and "storage units".

    I have used the "Data" tab "Data Validation" field to set up each individual list, but I'd like to set the 'drop-down' lists for "rooms" to be based upon the "buildings" value. How do I go about setting up the correct expression to do this?

    To re-phrase my question, think about the countries in North America (Canada, USA, Mexico). Each has Provinces or States. All have cities. However, if I have already specified a country, when I go to select a Province or State, I want the drop-down list to 'narrow' the values to those which make sense to Country. Further, once I have specified a Province or State, I'd like the City list to only give values for that Province or State.

    Thank-you for your assistance.

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

  3. #3
    New Lounger
    Join Date
    Nov 2009
    Location
    The Frozen Tundra
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Hello:

    I had viewed those instructions. They appear to be more or less specific to Excel 2003. I'm looking for the Excel 2007 specific process. I can set the first level of the Data List process, but not the Validation level. As near as I can discern, the process in the diagrams is not the same in Excel 2007.

    I have two worksheets in my Excel workbook. They are 'Inventory' (where the line by line records will be entered) and 'Controls' (where the 'drop-down' list values will be stored). On the 'Control' worksheet, 'Building' are in column G, 'Room' are in column H, and 'Unit' are in column I. Data in each of these columns is referenced as a named range ('Building', 'Room', and 'Unit' respectively).

    Can you point me to an Excel 2007 specific instruction?

    Thank-you

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It should be similar in Excel 2007 (which I don't have myself) - Data Validation is in the Data Tools section of the Data tab of the ribbon.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I used the referenced instructions and put this together in 2007.
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Nov 2009
    Location
    The Frozen Tundra
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello:

    Thank-you to those who provided replies and assistance. I have arrived at a suitable solution. The first step was to abandon the premise that I could use an array to set up my validations. (While an array solution might be possible, it is beyond my current Excel abilities.)

    I have set up my inventory workbook using two worksheets. The first worksheet is the data entry ('Inventory') worksheet. It has all the columns required to meet my recording needs. The second worksheet is the data look-up values ('Controls') worksheet. It has the information that supplies the 'drop-down' values for the 'Inventory' worksheet (thus saving keying time and increasing data accuracy).

    The controls are set up according to named ranges arranged by columns. By limiting the values to columns, the information is kept 'flat'.

    For the 'high-level' details, such as 'building', the named range ('Building')is set up on the 'Control' sheet. On the 'Inventory' worksheet, the Data Validation is set to Allow a 'List' as the Allow type and '=Building' (the Control named range) as the Source.

    For the secondary details (such as a 'Room' within a 'Building'), I (again) put the information into columns as named ranges. On the Inventory sheet (where separate columns for 'Building', and 'Room', I set the Data Validation for 'Room' to Allow a 'List' where the Source was set to use the '=INDIRECT(SUBSTITUTE(*cell*," ","")) statement. (The '*cell*' was where I referenced the 'Building' cell for 'Secondary' information. The series of quotes allows the spaces from a 'Building' name to be taken into account when referencing the Named Range (named ranges do not permit spaces).

    All in all, it works rather well.

Posting Permissions

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