+ Reply to Thread
Results 1 to 3 of 3

Using 'INDIRECT' function and dependent lists

  1. #1
    Registered User
    Join Date
    10-13-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    28

    Using 'INDIRECT' function and dependent lists

    Hi Everyone,

    I have a spreadsheet I'm developing that is using data validation > lists. The lists below 'Product' are all dependent upon the contents of the fields above (with bold outlines). I am using the 'INDIRECT' function and named ranges to 'filter' (for lack of a better word) what appears in each pick list so it depends on the contents of the cells above (first pic on the left). For example, if 'EL' and 'MD' are chosen, then the only Utility that should appear in the list should be BG&E. If 'EL' & 'PA' are chosen, then 'DUQ, Met-Ed, PECO, PPL' should appear as options in the pick list (pic on the right) because the formula for the list in cell A8, in the first attachment is '=INDIRECT(""&$A$5&"_"&$A$2)' and refers to the named range in the order of 'State' then 'Product'. I hope this explanation makes sense.

    My question is how do i get the picklists to automatically refresh when the contents of a cell above changes. So if i first choose 'BGE' when 'EL' and 'MD' are chosen above, and then pick 'BG&E' for the utility (it is the only one that is listed based on the criteria above) but then I want to change the state to 'PA', it still leaves in 'BG&E' in the Utility cell even though it should no longer be listed. You must go back to the Utility picklist and re-choose a PA Utility.

    It would be ideal if 'BG&E' automatically disappeared if the cell above changes to another state, like 'PA' for example.

    If this doesn't make sense I can try to post the file. Thanks in advance for any and all help! You guys are great!
    Attached Images Attached Images

  2. #2
    Registered User
    Join Date
    05-11-2016
    Location
    Liberty Lake, Washington
    MS-Off Ver
    2010
    Posts
    79

    Re: Using 'INDIRECT' function and dependent lists

    You have to use VBA to clear the contents of the dependent/child cells when a parent cell is edited.
    You can go a step further and write VBA to automatically fill in certain selections if there are only one choice... for example: If State = MD, Utility = BG&E.

    Do you know how to write that code?
    If not, upload your sheet and I can give you a hand.

  3. #3
    Valued Forum Contributor
    Join Date
    01-08-2011
    Location
    Portsmouth, England
    MS-Off Ver
    Excel 2007 to 2016
    Posts
    456

    Re: Using 'INDIRECT' function and dependent lists

    An alternative is to prevent the Product or State cells being changed if a Utility has already been selected. This forces the user to select things in the right order.

    So for example, in the validation in A2, you can say

    =IF(A5="",[your lookup details],INDIRECT("Block Change"))

    The range "Block Change" doesn't actually exist (you can call it anything you want), but it is an effective way of "locking" changes. You can apply the same principle to each level.

    It might not be exactly what you want, but is an alternative to using VBA.
    Excel is a constant learning process and it's great to help each other. If any of us have helped you today, a click on the "reputation" star on the left is appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Create Dependent and Indirect lists from non-adjacent cells
    By Ochimus in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-14-2016, 08:04 AM
  2. Dependent Data Validation - Indirect Function
    By Hudson in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-12-2014, 12:25 PM
  3. [SOLVED] Huge problem with the INDIRECT function, trying to create dependent drop downs
    By Quillow in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2013, 03:27 PM
  4. Replies: 3
    Last Post: 11-07-2012, 10:26 AM
  5. Dependent Lists with Index function
    By penguintar in forum Excel General
    Replies: 3
    Last Post: 03-28-2011, 04:28 AM
  6. Dependent lists/Indirect and multiple workbooks
    By Excel_Learner42 in forum Excel General
    Replies: 4
    Last Post: 12-31-2008, 02:20 PM
  7. Alternative to INDIRECT with Dependent Lists
    By RDSProgrammer in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-05-2007, 03:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1