Data selection from one worksheet into a second worksheet.

[Rosmirlea]

I am developing a job scheduling workbook and have the following problem:-

I have two worksheets and want to insert some of the data from the second sheet back into the first sheet.

Sheet 1

A B C
1 Date Day Inspection phase(s)
2 13/11/12 Tue
3 14/11/12 Wed
4 15/11/12 Thu
5 16/11/12 Fri
6 17/11/12 Sat
7 18/11/12 Sun
8 19/11/12 Mon
9 20/11/12 Tue

Sheet 2

A B C D E
Inspection phase 13/11/12 14/11/12 15/11/12 16/11/12
Tue Wed Thu Fri
1 Research 1
2 Analyse 1
3 Plan 1
4 Produce 1 1
5 Review 1 1 1

I want a function or an amalgamation of functions that returns the inspection phase(s) marked with 1 on sheet 2 to the date quivalent box on sheet 1.

i.e. for the date 13/11/12 I want the inspection phases called Research and Review returned to cell C2 on sheet 1.

Likewise for 15/11/12 I want the inspection phases called Plan, Produce and Review returned to cell C4 0n sheet 1.

I have tried various combinations of MATCH, INDEX and VLOOKUP functions but without success.

Any advice gratefully received.

Many thanks.

 

[isabelle]

hi,

if there are only two values ​​by date

=INDEX(Sheet2!A:A,MATCH(1,INDIRECT("Sheet2!"&ADDRESS(1,MATCH(A2,Sheet2!$1:$1,0))&":"&
ADDRESS(65536,MATCH(A2,Sheet2!$1:$1,0))),0))&"-"&INDEX(Sheet2!A:A,MATCH(1,INDIRECT("Sheet2!"&
ADDRESS(1,MATCH(A2,Sheet2!$1:$1,0))&":"&ADDRESS(65536,MATCH(A2,Sheet2!$1:$1,0))),1))


otherwise it may be required a custom function (vba)