How to display a cells data depending on date

[Kyroc]

Hello all, hopefully someone can help me with this.

I have a worksheet that has dates across row 1 for a year. In column A I have types of hours going down Eg: Normal, Overtime, Annual Leave, Sick Leave etc, with an amount for each date.

On a separate worksheet, how can I get it to display the figure in a particular cell depending on the date.

For example, if I put the date of 06/01/14 into a cell, how can I make the cell below that date show the sick leave figure for that particular date from the worksheet with all the data on it?

I hope that makes sense, I would post a sample table but cant get it to display properly.

<colgroup><col><col span="38"></colgroup><tbody>
</tbody>

 

[Oeldere]

Please show the format of 5 lines for 2 days on the forum.

 

[Kyroc]

	1/06/2014	2/06/2014	3/06/2014	4/06/2014	5/06/2014	6/06/2014	7/06/2014
Normal	85	103	50	97	121	37	80
RDOW	0	0	16	0	8	0	8
Sick	8	8	16	0	16	8	8
Annual	16	16	16	24	24	24	24
TIL	0	8	8	0	0	0	0

<colgroup><col><col><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

So on another page if I put in a cell 05/06/2014 I want the cell below it to display the sick leave figure for this date, which in this instance is 16

 

[drp92]

Use this to insert the table from advance mode.

http://tinypic.com/view.php?pic=206yhx4&s=8

 

[drp92]

Ok this will also do !

 

[drp92]

Try this
=INDEX(Sheet1!B2:H2,MATCH(Sheet2!$C$6,Sheet1!$B$1:$H$1,0))

If you want the data to kind of that looks like this :

Particulars	Data
Date	07-06-14
Normal	80
RDOW	8
Sick	8
Annual	24
TIL	0

<tbody>
</tbody>

Hope this helps !!

 

[Oeldere]

Index / match (already given as solution) wll do.

But I advice you to add the data in a verticaly structure (table form).

In that case you are able to use the powerfull Pivot Table.

 

[drp92]

Yes ! I completely agree with @Oeldere,

try this one instead "=INDEX($B$3:$H$7,MATCH($A13,$B$2:$H$2,0),MATCH(B$12,$A$3:$A$7,0))"
If you want the data to look like this !

lookup val	Ret value	Ret value	Ret value	Ret value	Ret value
Date	Normal	RDOW	Sick	Annual	TIL
01-06-14	85	103	50	97	121
02-06-14	0	0	16	0	8
03-06-14	8	8	16	0	16
04-06-14	16	16	16	24	24
05-06-14	0	8	8	0	0

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

(provided, the data is in same sheet, if not you can change that. Also the above formula is based on Source-data located from A2:H7)

Hope this will solve your pivot problem also.

 

[Kyroc]

Thanks all for your help. The index/match works perfectly.

Normally I would have used a pivot table however the way the data is set out makes it impossible.