+ Reply to Thread
Results 1 to 17 of 17

adding a new item to a named range option

  1. #1
    Registered User
    Join Date
    02-01-2004
    Location
    New Hampshire
    MS-Off Ver
    2002, 2003
    Posts
    50

    adding a new item to a named range option

    Hi All -
    I have a named range on a hidden sheet which populates data validation lists on 'sheet 1, cells b5-b55. My question is this...Is it possible to, upon entering a new item in b5 (for example), to have that item automatically added to the named range? (preferably through a button on a pop up form that opens w/ the question 'would you like to add to the list?, similar to an access form)...I've seen one example in the past which I found with a link from this site, but couldn't make it work. If the answer to my question is yes, could you also suggest a location of an example if you know of one?
    Thank You
    Last edited by -Brian-H-; 01-06-2009 at 08:40 PM.
    Brian

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You can use a dynamic named range.

    Say the current validation is is A1:A5 on the hidden sheet ("SheetX"). Then on the sheet where the validated cell is, define

    myVal Refers to: =index(SheetX!$A:$A, 1):index(SheetX!$A:$A, match("zzzz", SheetX!$A:$A, 1))

    Then use list validation and reference myVal as the validation range.
    Last edited by shg; 01-04-2009 at 02:25 PM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    02-01-2004
    Location
    New Hampshire
    MS-Off Ver
    2002, 2003
    Posts
    50
    the validated cells are on sheet2, could you tell me exactly what to do w/ this part? ...
    define myVal Refers to: =index(Sheet6!$c:$c, 1):index(Sheet6!$c:$c, match("zzzz", Sheet6!$c:$c, 1))
    thanks

  4. #4
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Another option, if you're not sure what the "last" value will be (e.g. trying to match to "zzzz") is to use Offset.

    Go to Insert->Name->Define

    The name is whatever you want, The formula is:
    Please Login or Register  to view this content.
    Which is:
    Please Login or Register  to view this content.
    -Bob

    Then just use DataValidation as normal to use your list, or however else you're using the named range.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Do Insert > Name > Define, then ...

    ... in the names box, type myVal

    ... in the Refers to box, paste =index(SheetX!$A:$A, 1):index(SheetX!$A:$A, match("zzzz", SheetX!$A:$A, 1)), changing SheetX to the name of the sheet where the validation list is.

    The reason to use INDEX rather than OFFSET is that it is non-volatile, i.e., if you you OFFSET, all cells dependent on the validated cell will recalculate every time anything in the workbook calculates.
    Last edited by shg; 01-04-2009 at 06:51 PM.

  6. #6
    Registered User
    Join Date
    02-01-2004
    Location
    New Hampshire
    MS-Off Ver
    2002, 2003
    Posts
    50
    i get error code...'The source currently evaluates to an error, do you wish to continue?'

    Please Login or Register  to view this content.
    the list is on sheet 6, column C, cell C1 being the heading

    btw thank you both, and i got this error trying both options.

  7. #7
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Quote Originally Posted by shg View Post
    The reason to use INDEX rather than OFFSET is that it is non-volatile, i.e., if you you OFFSET, all cells dependent on the validated cell will recalculate every time anything in the workbook calculates.
    So shg, taking that example further: what if you're not sure what the bottom value is going to be? Does match allow for finding your "key" value (e.g. zzzz) at the bottom and jumping back by 1 column? I'd personally hate to have a list whereby there was a redundant "this is the end of the list" value every time.

    The reason I ask is because I use Offset to generate a list of values chosen by a user. E.g. I have preset choices, and they can order them any way they choose. It could make a list that's "Val1, Val2, Val3" or "Val3, Val1, Val2" etc. By using Match, I couldn't search for any specific value, as I never know what it's going to be. That said, always on the look out for better ways to do things (that's why I'm here after all!) so any illumination you can provide is appreciated.

    Bob

    Edit: Brian, check your sheet names to make sure they exactly match. If there's any spaces, you'll need to enclose the sheet names in single quotes ('Sheet 6') etc. I've just copy pasted the formula above, created a list in Column C and it worked fine. As an aside, it includes the whole list, even though I didn't include a value "zzzz" so I'm assuming Match uses the closest applicable value, like some of the VLookup settings?
    Last edited by beeawwb; 01-04-2009 at 10:54 PM.

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Quote Originally Posted by Bob
    So shg, taking that example further: what if you're not sure what the bottom value is going to be? Does match allow for finding your "key" value (e.g. zzzz) at the bottom and jumping back by 1 column? I'd personally hate to have a list whereby there was a redundant "this is the end of the list" value every time.
    "zzzz" is not a key value; it returns the index of the last text value in the list, in the same way that =match(9.9E307, SheetX!$A:$A, 1) would find the last numeric value.

    Brian, you can post a workbook if you need some help setting this up.

  9. #9
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Quote Originally Posted by shg View Post
    "zzzz" is not a key value; it returns the index of the last text value in the list, in the same way that =match(9.9E307, SheetX!$A:$A, 1) would find the last numeric value.
    So realistically, all you're doing is specifying a value that is highly likely to be the greatest value in the list (MATCH returning the greatest value equal to or lesser than the lookup, when using match type 1). A list with zzzzz would return a list stopping at that value, as it's greater than zzzz. That's what I was trying to understand.

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Sure. If you commonly have text like "zzzz" in your list, you could use instead rept("z", 255).

    Similarly, if your list contains big numbers, you could be rigorous and use =match(9.99999999999999E+307, ...).

    I have defined constants (conZzz and conBigNum) in book.xlt and sheet.xlt for this purpose.

  11. #11
    Registered User
    Join Date
    02-01-2004
    Location
    New Hampshire
    MS-Off Ver
    2002, 2003
    Posts
    50
    does anyone know what would cause the error i posted above?

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    In the validation for the cell, use type List, and as the source enter myVal, which you defined as previously explained.

    If that doesn't work for you, post a workbook and I'll do it.

  13. #13
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105
    Quote Originally Posted by -Brian-H- View Post
    does anyone know what would cause the error i posted above?
    I was able to get that error when the sheet names used aren't correct. E.g. if you had 'Sheet 6' instead of 'Sheet6' As shg said, post a workbook if having difficulties.

  14. #14
    Registered User
    Join Date
    02-01-2004
    Location
    New Hampshire
    MS-Off Ver
    2002, 2003
    Posts
    50
    here's a sample, 'row A' validation lists. 'list' dynamic named range. I've tried adding an additional word to a cell in row A, but it doesn't transfer to the 'list'. does the code look right to you?
    Attached Files Attached Files

  15. #15
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    If that's what you wanted, I misunderstood.

    If you add another item to the list on the list tab, it will automatically appear in the validation drop-down, which is what I thought you wanted.

  16. #16
    Registered User
    Join Date
    02-01-2004
    Location
    New Hampshire
    MS-Off Ver
    2002, 2003
    Posts
    50
    is it possible (and easy ) to automatically have a word added to the list page when the user types a new word in a cell which has a validation list? 'automatically' may not be the right word, ideally, a pop up form will open saying something like "this word isn't in the list...add it?"
    thx

  17. #17
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    It's easy enough, but would require VBA. You can post in the programming forum if that's an acceptable solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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