Posts by hollyj

hollyj · Jun 6th 2016

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.

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

hollyj · May 13th 2015

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

GREAT THANKS that work perfectly. I really appreciate all your help!!!

hollyj · May 12th 2015

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

forum.ozgrid.com/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]

[/tr]

[tr]

[td][/td]

[td]

Tailings

[/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td]

Hydrology

[/td]

[td][/td]

[/tr]

[tr]

[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.

hollyj · May 12th 2015

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

Hello, That works great, thanks so much.

If it's not too much trouble what can I add to the formula to find all the values for example Tim Newman has a level 2 skill level for Tailings and Water.

hollyj · May 11th 2015

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]

Hydraulics

[/td]

[td]

Tailings

[/td]

[td]

Water

[/td]

[td]

Hydrology

[/td]

[/tr]

[tr]

[td]

6

[/td]

[td]

Name

[/td]

[td]

Surname

[/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]

[/tr]

[tr]

[td]

2

[/td]

[td]

Firstname

[/td]

[td]

Holly

[/td]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

Surname

[/td]

[td]

Jenkinson

[/td]

[td][/td]

[/tr]

[tr]

[td]

4

[/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]

[/tr]

[tr]

[td]

7

[/td]

[td][/td]

[/tr]

[tr]

[td]

8

[/td]

[td][/td]

[/tr]

[tr]

[td]

9

[/td]

[td][/td]

[/tr]

[tr]

[td]

10

[/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.