returning value based on date

[Leavesfallfar]

Hi,

I have an hours sheet tab with details of date and hours, such as:

	01/01/2016	02/01/2016	03/01/2016	04/01/2016	05/01/2016
	B	C	D	E	F
STAFF NAME
Andy	6	2	4	6	5
Becky	6	1	4	6 ​	7

<tbody>
</tbody>

I also have a daily summary sheet which I want to automatically return hours value for the staff member and date in question

todays date	01/01/2016	B
	Time Allocated	Time Taken
Bob	5
Norman	4

<tbody>
</tbody><colgroup><col><col><col></colgroup>

What formula can I use to do this? I have tried =IF(C11="","",COUNTIF(INDIRECT("'Staff Hours'!"&CONCATENATE($Q$7,":",$Q$7)),""))
(staff name i.e andy) (count) (staff hours sheet) (character assignment to date)

but it is returning an incorrect value of 1048563! instead of 6

is there a simple way to do this? any help would be greatly appreciated

 

[Marcílio_Lobão]

Leavesfallfar, Good afternoon.

Suppose:

Staff Hours Sheet

	A	B	C	D	E	F
1			WORKDAYS
2	STAFF NAME	01/01/2016	02/01/2016	03/01/2016	04/01/2016	05/01/2016
3	Andy	6	2	4	8	5
4	Becky	9	1	7	6	7
5	John	5	6	7	8	9
6	Marie	8	7	6	5	4

<tbody>
</tbody>

Summary Sheet

	A	B
1		WORKDAY
2	STAFF NAME	01/01/2016
3	Andy	6
4	Becky	9
5	John	5
6	Marie	8

<tbody>
</tbody>

Try to use:

Summary sheet --> B3 --> FORMULA

=INDEX('Staff Hours'!$B$3:$F$6, MATCH(A3, 'Staff Hours'!$A$3:$A$6, 0), MATCH($B$2, 'Staff Hours'!$B$2:$F$2, 0))

Copy it down.

Is that what you want?
I hope it helps.

 

[Leavesfallfar]

leavesfallfar, good afternoon.

suppose:

staff hours sheet

	a	b	c	d	e	f
1			workdays
2	staff name	01/01/2016	02/01/2016	03/01/2016	04/01/2016	05/01/2016
3	andy	6	2	4	8	5
4	becky	9	1	7	6	7
5	john	5	6	7	8	9
6	marie	8	7	6	5	4

<tbody>
</tbody>

summary sheet

	a	b
1		workday
2	staff name	01/01/2016
3	andy	6
4	becky	9
5	john	5
6	marie	8

<tbody>
</tbody>

try to use:

summary sheet --> b3 --> formula

=index('staff hours'!$b$3:$f$6, match(a3, 'staff hours'!$a$3:$a$6, 0), match($b$2, 'staff hours'!$b$2:$f$2, 0))

copy it down.

Is that what you want?
I hope it helps.

thank you, thank you, thank you it worked a treat you sir are a genius!!

 

[Marcílio_Lobão]

Thanks for the feedback.
Glad to be of help.

 

[Leavesfallfar]

Hi Marcilio would you be able to help with another issue please

Say I have a sheet with a job


1/1/16 2/2/16
zoo job zoo job
Andy monkey
Bernard banana


is there a way to return an iftext argument or such on the daily summary sheet so that if the date is 1/1/16 it will return the text entered into the job list for that day? i.e. banana but obviously this would need to link in with the changing date data, so if it was the 2/01/16 it would display monkey

 

[Marcílio_Lobão]

Leavesfallfar, Good evening.

I'm sorry but I can't understand how is your layout.

Please, try to show us your layout a little more helpful.

I'm sure we can help you again.

 

[Leavesfallfar]

	A	B
1	Staff	1/1/2016	2/1/2016	3/1/2016	4/1/2016
2	George	HZ			HS
3	Bob		HS
4	Bernard	FS		DR
5	Jim		DR	DR

<tbody>
</tbody>

Sorry for the Confusion! I will try to explain myself better.

Say we have the above information on a workbook tab called 'fault codes'

 

[Leavesfallfar]

A	B	C	D	E
1	TODAYS DATE	STAFF WITH FAULT CODES
2	1/1/2016	GEORGE
3		BERNARD
4

<tbody>
</tbody>

Then we have a workbook tab daily summary.

I need a formula that would match the date in B2 on the daily summary to the date in b1:f1 and return staff name if there is any text on that day in the fault code tab sheet.

i.e. for the 1/1/2016 b2 contains 'HZ' so would want a2 George displayed on the daily summary sheet. I know there is an iftext function in excel so wondered if there is a formula that in cell b2 contains any text then the daily summary sheet would reference a2 - based on the date range

I hope this is possible, thanks for your help