Excel Is this possible??

[Simont485]

On sheet 1
I have a staff rota that has about 50 names listed in column A and dates across row 1. (50*365 cells)

on sheet 2

The dates will be across the top as per sheet one

I would like to input a name in cell A3 and that persons rota for the year appears in row 3.

and so on for each person on the rota when a name is entered......

Index and match? I have tried but to no avail!

 

[Simont485]

Ok I have solved this.....Vlookup combined with Columns function to catch all data....seems to work!

 

[nivrip]

Glad you got it.

And it may help someone else.

 

[Simont485]

For reference formula I used =vlookup(A1,sheet1A3:NK50,Columns($A$3:A3)+1,False) So it does the look up but, as I have a large spreadsheet , instead of entering a column number I have used the Columns functions so that the column number increments when I drag/copy the formula across the spreadsheet! My only frustration is that, as with Index/Match and linking(=sheet1,A1), Vlookup only retrieves the data and not any formatting(cell colour, comments etc).....it would be useful to have this as an option!