Posts by hollyj

    I have attached a sample file to help with my problem


    I have two worksheets one called output and one called data.


    I would like to populate columns B and C in the output worksheet using data from the data worksheet.


    The user should choose an option from the dropdown menu in cell B2 on the output worksheet sheet.


    I would like to show all the training done in column B and the date the training was done in column C.


    A sample answer would be


    If "A" was selected from cell B2


    [TABLE="class: grid, width: 300, align: left"]

    [tr]


    [td]

    Training

    [/td]


    [td]

    Date

    [/td]


    [/tr]


    [tr]


    [td]

    SI

    [/td]


    [td]

    June-12

    [/td]


    [/tr]


    [tr]


    [td]

    VST

    [/td]


    [td]

    Jan-13

    [/td]


    [/tr]


    [tr]


    [td]

    PWW

    [/td]


    [td]

    Sept-13

    [/td]


    [/tr]


    [tr]


    [td]

    TS

    [/td]


    [td]

    Nov-13

    [/td]


    [/tr]


    [/TABLE]









    I hope someone can help.


    ozgrid.com/forum/core/index.php?attachment/69337/

    Re: Lookup Row, Then Lookup Value In Row & Return Column Header: Two Way Lookup


    ozgrid.com/forum/core/index.php?attachment/65185/


    Thanks for the help its is very much appreciated. I'm afraid I need the data to be kept in columns so all level 4 skills will be displayed vertically underneath the level 4 heading and all level 3 skills under the level 3 heading etc.


    For example Holly Jenkinson might have three skills in level 5. I would like them displayed vertically as below. I have done this before when searching rows and returning values horizontally using the small and if function. I am little confused as now I'm searching columns and returning values vertically.


    [TABLE="width: 500"]

    [tr]


    [td]

    Skill

    [/td]


    [td]

    5

    [/td]


    [td]

    4

    [/td]


    [td]

    3

    [/td]


    [td]

    2

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Water

    [/td]


    [td][/td]


    [td]

    Hydraulics

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Tailings

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Hydrology

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]



    I have done something similar before when searching rows and returning values horizontally using the small and if function. I am little confused as now I'm searching columns and returning values vertically.

    Files

    • skills.xlsx

      (10.9 kB, downloaded 59 times, last: )

    I have two worksheets in excel my first sheet looks like this showing staff names and skills numbered from 1-5. The numbers represent the level of skill they have in that area of work.e.g Holly Jenkinson has a skill level 4 for tailings and skill level 3 for water.


    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    a

    [/td]


    [td]

    b

    [/td]


    [td]

    c

    [/td]


    [td]

    d

    [/td]


    [td]

    e

    [/td]


    [td]

    f

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td][/td]


    [td][/td]


    [td]

    Hydraulics

    [/td]


    [td]

    Tailings

    [/td]


    [td]

    Water

    [/td]


    [td]

    Hydrology

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Name

    [/td]


    [td]

    Surname

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    Holly

    [/td]


    [td]

    Jenkinson

    [/td]


    [td]

    1

    [/td]


    [td]

    4

    [/td]


    [td]

    3

    [/td]


    [td]

    5

    [/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    Tim

    [/td]


    [td]

    Newman

    [/td]


    [td]

    0

    [/td]


    [td]

    2

    [/td]


    [td]

    2

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    Louise

    [/td]


    [td]

    Cooper

    [/td]


    [td]

    1

    [/td]


    [td]

    5

    [/td]


    [td]

    4

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    Lee

    [/td]


    [td]

    Lynch

    [/td]


    [td]

    4

    [/td]


    [td]

    2

    [/td]


    [td]

    3

    [/td]


    [td]

    5

    [/td]


    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    Elaine

    [/td]


    [td]

    Fisher

    [/td]


    [td]

    0

    [/td]


    [td]

    0

    [/td]


    [td]

    2

    [/td]


    [td]

    3

    [/td]


    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td]

    Mark Dillon

    [/td]


    [td][/td]


    [td]

    1

    [/td]


    [td]

    5

    [/td]


    [td]

    4

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [/TABLE]



    my second sheets looks like this.The idea is that cell B2 and B3 are drop down boxes containing all the firstnames and surnames from sheet one.Cells B5:F5 represent the skill levels numbered from 1-5.


    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    a

    [/td]


    [td]

    b

    [/td]


    [td]

    c

    [/td]


    [td]

    d

    [/td]


    [td]

    e

    [/td]


    [td]

    f

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Firstname

    [/td]


    [td]

    Holly

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Surname

    [/td]


    [td]

    Jenkinson

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    Skill

    [/td]


    [td]

    5

    [/td]


    [td]

    4

    [/td]


    [td]

    3

    [/td]


    [td]

    2

    [/td]


    [td]

    1

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    I want to search using the surname and skill level in sheet one and return the name of the skills in sheet 2 e.g If I select Jenkinson in cell B6 want to select all the skills she has at level 5. Then in cell C6 all the skills at level 4 etc etc. Therefore in cell B6 I should return values Hydrology and in cell C6 I should return values tailings.