Auto populate cells to the right of a dropdown box

    Hi I'm really struggling to fine a solution to this problem.


    I have a team sheet for rugby that requires completing weekly.


    I have created a player database to the right of the team sheet.


    I have used Data Validation to create a drop down list for the players surname.


    What i'm trying to achieve, is if I select a player surname, the rest of the data in the players database populates across to the team sheet.


    I hope this all makes sense, and someone can advise me on this.


    Thanks in advance.

    Re: Auto populate cells to the right of a dropdown box


    vlookup should do that for you


    you are not using sheet 2 at all - you could have the database elesewhere


    put in E24
    =IF($C24="","",VLOOKUP($C24,$N$1:$Y$38,2,FALSE))


    change the ,2, to the column you want to look up , as you move across the row ,


    see attached

    ETAF

    Re: Auto populate cells to the right of a dropdown box


    Thank you so much. This is exactly what i needed.


    I have removed the merged cells.


    How do i move the database to a separate sheet, but keep the formulas?

    Re: Auto populate cells to the right of a dropdown box


    Just use the existing data on sheet 2, changing the formula to suit the slightly different layout.



    NOTE:
    I've removed the attachment - the data on sheet 2 was too good to be a sample.

    Re: Auto populate cells to the right of a dropdown box


    Noticed cytop has removed attachements


    I have EDITED - I have removed DoB, Age, email, phone for dummydata and removed the sheet2


    you can use the sheet name infront of the range


    say you added to sheet 3
    then
    =IF($C24="","",VLOOKUP($C24,Sheet3!$N$1:$Y$38,2,FALSE))


    Now i have moved to a new sheet and renamed the sheet


    i have also made the data in that sheet into a table and refered to the table name - which now allows you to add new members to the data and the lookups will automatically update to include the new range
    BUT that may only work in version 2010


    what version do you have and run ?
    I may have helped tofar in the example




    Older versions 2003 - use List and can do the same - so i can talk you through



    Re: Auto populate cells to the right of a dropdown box


    I'm using 2010. I appreciate the help, but I'm now confused as to what has been done.


    The first example was perfect, but it would help if the player database sat in sheet 2.


    Been able to add extra players, without messing up the formulas would be great.


    I'm not sure what happened to the date of birth and age, it's not calculating correctly on the 2nd example sheet.

    Re: Auto populate cells to the right of a dropdown box


    you just need to format those cells J24 onwards as a date - its returning the date , but displaying as a number - so a simple format will solve that
    the age is correct - I simply changed to a number in the table -so a quick way to get rid of all the "real" data, was just to put, 1,2,3 etc - have a look and change to the real dob and age , as this is a public forum , you should not put any personal data or confidential data online as its part of data protection act etc


    I have renamed the sheet - so it could have been called sheet2 - i just thought TeamData was better
    then all you needed to do was add
    sheet2! infront of the range
    =IF($C24="","",VLOOKUP($C24,Sheet2!$N$1:$Y$38,2,FALSE))
    as above


    as i moved to a new table - all the namedranges have #ref now and so I added a named range called Surname so the dropdown list would also extend


    but the range that is used was fixed to $N$1:$Y$38
    so if you add a new entry in row 39
    you would need to update all the formulas in the vlookup to now show     $N$1:$Y$39

    in 2010 you can over come that issue , by using a table so I selected the area     $N$1:$Y$38 and applied a table to it and rather than have the default table1 or table2 I choose to rename the table to something meaning full and called it TeamDataTBL
    Now instead of having to put in to a formulas Sheet2!$N$1:$Y$38 I can just put the name TeamDataTBL ( like named ranges) only now when you add to the bottom of the table it will automatically update and be included in the range named TeamDataTBL and so the vlookup continues to work and include the additional rows

    hope that helps -if not ask some more questions

    see attached


    i hope I have not confused further

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login