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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Leavesfallfar

New Member
Joined
Oct 30, 2016
Messages
9
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!!
 

Leavesfallfar

New Member
Joined
Oct 30, 2016
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Oct 7, 2013
Messages
777
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Oct 30, 2016
Messages
9
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:

Leavesfallfar

New Member
Joined
Oct 30, 2016
Messages
9
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,483
Members
417,027
Latest member
wlknspc7

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
Top