Results 1 to 11 of 11
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Indirect validation list range (Excel2000)

    I am happily using indirect validation list ranges where, based on the setting of an adjacent cell, the dropdown list alongside shows one of three corresponding lists.
    I need to now have a further selection to cover the contingency of all entries in the three lists.
    For example, if my adjacent cell contains now contains the word FULL, I want the drop down validation list alongside to now show all entries from ListA, ListB and ListC. They don't need to be re-sorted, i.e. keep the lists grouped as before.

    Is there a simple way to specify this 'combined full list' as my validation list range???
    Any help or comments much appreciated!

    zeddy

  2. #2
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect validation list range (Excel2000)

    This might work:

    While holding down the CTRL key, select all three lists.
    While the lists are selected, choose Insert==>Name==>Define from the menus.
    Create a name like ListFull and click the OK button.

    Note: this combined name is refering to specific cells. I don't think that this combined range definition will change if you change the range definition of any of the individual lists.

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Indirect validation list range (Excel2000)

    If that doesn't work, I'd combine all three primary lists into one with a lookup, then define the lookup range with a new range name.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Indirect validation list range (Excel2000)

    You can not do it if you select a non-contiguous range:

    You need to create a named range named full which is a list of all the entries in the 3 lists and indirect the name full.
    List A, ListB, List C have to be in 1 column 1 after the other: then select the start of List A and the last cell of List C and name it "FULL".

    Steve

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Indirect validation list range (Excel2000)

    Hi Steve,

    You are bang on again.
    Seems the list source for data validation must be a delimited complete list or a reference to a single column (or single row).

    The easiest would be to place them under each other in a single column as you suggested.
    Pain to automate the updating of these lists although I suppose I could colour code the background to show the three blocks clearly.

    Many thanks for everyone's interest!

    zeddy

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Indirect validation list range (Excel2000)

    One "relatively" simple way to base it on the combined ranges without having the ranges in one line:
    Build the FULL range in an empty column using formula and a dynamic range name
    I will make an example using column E (change as appropriate)
    In E1 enter the formula:

    =IF(ROW()<=ROWS(ListA),INDEX(ListA,ROW()),IF(AND(R OW()>ROWS(ListA),ROW()<=ROWS(ListA)+ROWS(ListB)),I NDEX(ListB,ROW()-ROWS(ListA)),IF(AND(ROW()>ROWS(ListA)+ROWS(ListB), ROW()<=ROWS(ListA)+ROWS(ListB)+ROWS(ListC)),INDEX( ListC,ROW()-ROWS(ListA)-ROWS(ListB)))))

    Copy this down from E2 to Ewhatever. It shuld be LONGER than the total. It does NOT matter if the range is LARGER than the sum of the 3 ranges, it will NOT be correct if it is smaller. Make it long enough to anticipate any "growth" in the sum of ListA, ListB, ListC.

    Define a name (insert name define:
    Name: CountFull
    Refers to: <pre>=ROWS(ListA)+ROWS(ListB)+ROWS(ListC)</pre>


    Name: Full
    Refers to: <pre>=OFFSET(Sheet1!$E$1,0,0,CountFull,1)</pre>


    How it works:
    E1-E whatever
    Checks the row number, if 1 - length of List A, it grabs the 1st -nth item in ListA.
    If number is from 1+length ListA to length of ListA+ListB it grabs 1-nth value of ListB
    If number is from 1+length ListA+ListB to length of ListA+ListB+ListC it grabs 1-nth value of ListC
    If >greater than length of ListA+ListB+ListC it gives a FALSE since I gave no response

    The named range "CountFull" adds the total number of values in the 3 lists
    The named range "Full", starts in Cell E1, moves no rows, no columns, and then uses the total number of items as its rows and has 1 column.

    So as you change List A, B and C, it will change the values in E which changes Full automatically. You only have to make sure you have sufficient rows copied in E for expansion.

    Steve

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: Indirect validation list range (Excel2000)

    Hi Steve,

    ..An excellant solution!
    This will be much easier to automate. Top banana! I love it.

    zeddy

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Indirect validation list range (Excel2000)

    I didn't discuss this (and you might have already done this), but you can use the offset function to make the LISTA, ListB, and ListC dynamic.

    For example if ListA is in Col A (A1:Awhatever)
    Define a name (insert name define):
    Name: CountListA
    Refers to:
    <pre>=COUNTA(Sheet1!$A:$A)</pre>


    Name:ListA
    Refers to:
    <pre>=OFFSET(Sheet1!$A$1,0,0,CountListA,1)</pre>


    Do the analogous things for listB and ListC. Change columns as appropriate. Note the lists must be the ONLY things in the columns!

    Now when you ADD items to the list the RANGE will automatically expand to include them. ListA, B, C will grow just BY ADDING the ITEMS. And the FULL list will change as the individual lists change

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect validation list range (Excel2000)

    I'd like to learn more about "indirect validation". Can you post a simple spreadsheet illustrating it?
    thanks

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Indirect validation list range (Excel2000)

    It is discussed here at this website and has an example.

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Indirect validation list range (Excel2000)

    Great site. Thanks

Posting Permissions

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