Extract column data by date

[Simont485]

ON spread sheet 1
I have a row of dates lets say B1:AB1
In A2:A8 I have a list of names.
In the cells B2:AB8 are letters representing shifts

On Spreadsheet 2
I have the same list of names
In B1 I want to enter a date the matches one of the dates on spreadsheet 1
in B2:B8 I want to see the left most letter that appear under that date in spreadsheet 1

When I change the date the data changes correspondingly.....


Basically one Column of shifts under one date instead of hunting for one date out of 365 columns of shifts

 

[Yongle]

Try:

All on sheet2

In B1 use Data Validation \ list \ with Source =Sheet1!$B$1:$AB$1

and formula in in B2 copy down
=INDEX(Sheet1!$A:$AB,MATCH(Sheet2!$A2,Sheet1!$A:$A,0),MATCH($B$1,Sheet1!$A$1:$AB$1,0))

 

[Simont485]

Wow that works!! Thank you.......but(sorry there had to be one)

If there is an empty cell I need it to be blank and not 0. Also I would like to show the left most character from sheet 1 only..

Thank you in advance

 

[Yongle]

In B2 copy down
=SUBSTITUTE(LEFT(INDEX(Sheet1!$B:$AB,MATCH(Sheet2!$A2,Sheet1!$A:$A,0),MATCH($B$1,Sheet1!$B$1:$AB$1,0)),1),0,"")

 

[Yongle]

This shorter formula should also work

=LEFT(INDEX(Sheet1!$B:$AB,MATCH(Sheet2!$A2,Sheet1!$A:$A,0),MATCH($B$1,Sheet1!$B$1:$AB$1,0)))