Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lists of Lists in Data Validation (Excel 2000 SP3)

    The attached workbook may explain better. Basically imagine you have TWO cells with Data Validation LIST set. Depending upon the value selected in the FIRST cell, I want to change the LIST that the second cell looks at. I can do this by hard coding the list names in the second cell via an IF command. While this works, it doesn't allow me to easily extend the number of LISTS that the second cell can look at, unless I want to write very complex IF statements - I don't..

    The attached workbook shows a method for programatically determining the appropriate LIST name which works. What I cannot see a way to do is to take the list name that is generated and make it refer to the LIST/Defined Name as opposed to just displaying a single entry which is the calculated LIST/Defined Name itself. Can this be done without VB code?

    Cheers
    Peter

  2. #2
    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: Lists of Lists in Data Validation (Excel 2000 SP3)

    Check out MS MVP Debra Dagliesh's example in Excel -- Data Validation -- Dependent Lists. I think this is what you are after.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lists of Lists in Data Validation (Excel 2000

    Thanks Steve... I can make the suggested approach work by modifying the way I generate list references, so problem solved. But.....

    As much as a learning excercise for myself, is there any way I can modify Debra's approach to work with lists defined OUTSIDE of the Data Validation settings for an individual cell? The three Lists in the original example spreadsheet are Defined Names as follows:

    lstHardware = OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D$2:$D$21),1 )

    lstSoftware = OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$21),1 )

    lstServices = OFFSET(Sheet1!$H$2,0,0,COUNTA(Sheet1!$H$2:$H$21),1 )

    Defining the Lists in this way means I can use them in other calculations outside of Data Validation inside a cell - I appreciate I could still define my lists the way I want to but use Debra's approach inside a cell that requires Data Validation.

    Presumably the key here is that Debra's approach always resolves to a physical cell reference, whereas my preferred approach doesn't? i.e if say cell A1 contains the calculated value "lstServices" - without the quotes - that =INDIRECT(A1) in a Data Validation setting will never actually refer to my list?

    As I said above, your/Debra's suggested approach will work for me.. I'm just curious... Many thanks.

    Peter

  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: Lists of Lists in Data Validation (Excel 2000

    I am not exactly sure of your question. Debra indicates how to use dynamic lists and even has a sample file...

    Or are you asking a different question?

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lists of Lists in Data Validation (Excel 2000

    Hi Steve

    Sorry, I should try to be clearer. I think what I'm trying to understand is how I might work with these lists outside of Data Validation entries. I'm working on something that may have 30-40 interchangeable lists. Imagine you have the following two Defined Names

    lstHardware = OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D$2:$D$21),1 )

    lstAnotherList = Sheet1!$D:$D

    Debra's approach WILL work with "lstAnotherList" but will NOT work with "lstHardware". I understand that this is because "lstHardware" is a formula and not a reference? e.g. If A1 contains "lstAnotherList" and A2 contains "lstHardware". The command:

    =OFFSET(INDIRECT(A1),0,0,COUNTA(INDIRECT(A1)),1)

    WILL work, whereas the command:

    =OFFSET(INDIRECT(A2),0,0,COUNTA(INDIRECT(A2)),1)

    Does NOT work.

    Debra's approach works excellently for dynamic lists and the Data Validation settings. The way I usually define lists, as described by "lstHardware" above is fine if you are going to use the Defined Name directly in code, but NOT if you want to use the name dynamically in code. e.g. Sticking with the Defined Names above and A2 containing "lstHardware", the command:

    =MATCH("Some Value",INDIRECT(A2),0)

    Does NOT work, whereas the command:

    =MATCH("Some Value",lstHardware,0)

    WILL work.

    In essence I think what I'm coming to understand is that if I define lists as I do for "lstHardware" above, there isn't any way Excel will allow me to reference them dynamically. e.g If the value in A2 above changes. The way round it I guess is to extend Debra's approach to Defining Names and recode the MATCH formula, and any others I need kind of like so:

    =MATCH("Some Value",OFFSET(INDIRECT(A1),0,0,COUNTA(INDIRECT(A1) ),1),0)

    Where A1 contains "lstAnotherList" as defined above. It'll work, but is it the most effecient approach?

    Am I making sense yet? Cheers again!

    Peter

  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: Lists of Lists in Data Validation (Excel 2000

    Outside of datavalidation, there should not be much a problem. the problem is that the datavalidation "evaluator" seems to be limited. It can not use indirect with a name that is a formula, it must be named cell.

    I am confused by your comment that:
    =MATCH("Some Value",INDIRECT(A2),0)

    "Does NOT work". It works for me as a formula in excel. It does not work if it is a formula in datavalidation (since A2 references a formula and not a cell). It is a datavalidation issue.

    I think the only way around this is what Debra listed, using the formula in the datavalidation formula, not in the named range, if you are using datavalidation to display a list.

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lists of Lists in Data Validation (Excel 2000

    Hi Steve

    I think I've forced this thread way of topic.. It now has nothing to do with variable lists as such. The attached spreadsheet shows what happens if you define a dynamic list purely via a Defined Name and then try to reference it in any way other than by directly hardcoding the name of the list. i.e. I don't think any Function in Excel can accept an INDIRECT formula as a pointer to a list, INDIRECT MUST always resolve to a cell reference for it to work?

    It's not an issue to me as such, I can see how to code my around it by using Debra's approach in other (Non Data Validation) formulas. I'm just wanting to make sure I'm not missing something blindingly obvious?

    Thanks again for indulging my learning!

    Cheers
    Peter

  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: Lists of Lists in Data Validation (Excel 2000

    No, you are correct. I thought it was a datavalidation issue. It appears that indirect can use cell references and named ranges only. It can not resolve named formula even if they can be resolved into a name.

    It appears that a workaround must be used. Debra lists one, but there are others depending on what you are doing.

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lists of Lists in Data Validation (Excel 2000

    Thank Steve/Others

    So.. is there a better, more succinct way, to write stuff like:

    =MATCH(A10,OFFSET(INDIRECT(A9),1,0,COUNTA(INDIRECT (A9))-1,1),0)

    A9 contains the name of a list, defined as described by Debra.

    The model I'm working on will use lots of lists, often on a single sheet, with a fair amount of MATCHing and INDEXing going on. The portion of the above formula:

    OFFSET(INDIRECT(A9),1,0,COUNTA(INDIRECT(A9))-1,1)

    Is going to get repeated a lot - albeit with different cell references to A9. Without using VBA to write a custom function, is the above as good as way as any of coding the formula?

    Cheers
    Peter

  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: Lists of Lists in Data Validation (Excel 2000

    I guess it depends on what you are doing exactly.

    The issue appears to be limitations in INDIRECT. Do you have to use indirect or can you assign it a name and have the name itself vary location with an offset function.

    Or perhaps it may be easier to maintain the names as "non-dynamic" lists which do not have the problem with indirect.

    Steve

  11. #11
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lists of Lists in Data Validation (Excel 2000

    Hi Steve

    Interesting... I must do some work! But... I love a challenge also!. The following formula:

    =ADDRESS(ROW(lstHardware2)+1,COLUMN(lstHardware2), 1,,MID(CELL("filename",lstHardware2),FIND("]",CELL("filename",lstHardware2),1)+1,
    LEN(CELL("filename",lstHardware2))))&":"&ADDRESS(C OUNTA(lstHardware2),COLUMN(lstHardware2),1)

    Displays the result:

    Test!$E$2:$E$9

    Which is exactly what I would expect. i.e. It's the address range of the list in question. Violla I thought, now I can pass the specific cell range I want to use in a Defined Name rather than using the OFFSET technique.. If only life were that straight forward!

    Can you/anyone else watching help me understand what the difference is in using the formula to create a Defined Name as opposed to typing the sheet/cell addresses directly? i.e. Typing the sheet/cell address directly works and is supported by the INDIRECT function whereas the formula above behaves in eactly the same way as the OFFSET technique - it works if you specifically use the Defined Name you have created but NOT if referenced via INDIRECT..

    I've dug around in some of the work that Jan Karel Pieterse has published on using Excel 4 macros in Defined Names, and got very excited about things like TEXTREF (Sad I know), but that produces the same results as above. i.e. I can't quite seem to find a way to generate and pass the specific cell address to a Defined Name.

    Cheers
    Peter

  12. #12
    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: Lists of Lists in Data Validation (Excel 2000

    The limitation seems to be using indirect. Indirect seems only to work with text that looks like a range or a named range that refers to a range. A named formula that yields a range is not allowed.

    Most workaround would therefore involve not using indirect.

    Your formula result (Test!$E$2:$E$9) is text is not a range object. It requires indirect to make the text a range object.

    Offset creates a range object, not text so it does not require indirect

    Perhaps if you elaborated on what you needed we could suggest some options...
    Steve

  13. #13
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lists of Lists in Data Validation (Excel 2000

    Hi Steve

    This is just an intellectual exercise for me now.. As you say, there are work arounds that can circumvent the INDIRECT issue. Just wanted to be sure I wasn't missing a trick about forcing the Defined Name to somehow accept a text strings containing an absolute reference. Let's leave it be for now and I'll concentrate on some 'real' work as opposed to something which is simply 'fascinating'!

    Thanks for your help, advice and feedback. Greatly appreciated.

    Cheers
    Peter

  14. #14
    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: Lists of Lists in Data Validation (Excel 2000

    I lean towards not using dynamic names if the number of names is small and you have needs of lots of indirects (eg if you have lots of dependent validations)

    Or if you have limited numbers of different indirects, I would use a limited number of direct names and have them use offset to have them vary by column or location.

    Steve

  15. #15
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lists of Lists in Data Validation (Excel 2000

    Good advice, many thanks again.

    Cheers
    Peter

Page 1 of 2 12 LastLast

Posting Permissions

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