returning value based on date

Leavesfallfar

New Member
Joined
Oct 30, 2016
Messages
9
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
465
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 date01/01/2016 B
Time AllocatedTime Taken
Bob5
Norman4

<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 :)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Leavesfallfar, Good afternoon.

Suppose:

Staff Hours Sheet

ABCDEF
1WORKDAYS
2STAFF NAME01/01/201602/01/201603/01/201604/01/201605/01/2016
3Andy62485
4Becky91767
5John56789
6Marie87654

<tbody>
</tbody>

Summary Sheet

AB
1WORKDAY
2STAFF NAME01/01/2016
3Andy6
4Becky9
5John5
6Marie8

<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.
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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'
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,214,777
Messages
6,121,502
Members
449,036
Latest member
dudeinaghillie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top