Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    13

    Unanswered: Drop down box with link to data

    Hi...

    maybe someone can help me with this... or the very least tell me its impossible

    I have a spreadsheet with the following columns
    NAME POS PTS SAL

    What i am trying to do is create a dropdown box that allows me to select a name then have the rest of the data (POS, PTS, SAL) pop into the columns beside the dropdown box.

    Did that make any sense?

    So basically i want to be able to choose:

    Joe Smith(from dropdown) - then have the data the is associated with that name go into the 3 columns besides that drop down.

    Thoughts?

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    The answer is yes it is possible!

    I am sure there are a number of way of doing this but try this.

    For the drop list set the Input Range to be the NAME column range (ie A2:A7 for example) and set the Cell Link property to be the cell underneath the Drop List (ie not visibl).

    Next to the drop list enter the formula =INDEX(B2:B7,F4,1) for the POS Column and =INDEX(C2:C7,F4,1) for the PTS column and so on.

    In this example F4 is the Link Cell for the Drop List.

    As the drop list returns an index position of the selected item and not the value of the selected item, it would seem you need to use the INDEX function and not a LOOKUP or VLOOKUP.

    HTH


    MTB

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    Or depending on your needs you could use Validation. Data > Validation. Then in the top dropdown box choose "List". In the box below that put in your list:

    =$A2:$A7

    Or if the list will change in length, then use a dynamic named range.

    Insert > Name > Define

    Put you name in top box (no spaces), i.e Test, then in the Refers to box put this:

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

    That way your list will automatically adjust to what you have.

    Then in the Validation Refers to list, put

    =Test
    Last edited by shades; 10-06-04 at 15:22.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi shades

    I am not sure what you are trying to do with the Data > Validation part of you post, but the OFFSET function is quite interesting - never used the Worksheet OFFSET before then again the whole point is to lean something!! .

    Apart for my machine not liking the Sheet1!$A$, part (Sheet1!$A$2, is OK! - assuming the top row are headings?). Using the range name Test as the Input Range of the Drop List it does indeed keep track of both inserted rows AND appending data to the bottom of the column in the list box, just great.

    However the related Index functions I posted do not.

    Therefore I suggest these also use OFFSET as follows

    =INDEX(OFFSET(Test,0,1),F4,1)

    and

    =INDEX(OFFSET(Test,0,2),F4,1) etc

    This will also keep tabs on additions to the bottom of the list.

    Cheers

    MTB

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Hi, Mike. Yeah, I forgot the "1" in the formula. I had changed it, but got too quick with the mouse button and erased it.

    Here is the corrected formula:

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

    Which I edited in the post above.
    Last edited by shades; 10-06-04 at 15:18.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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