# returning value based on date

#### Leavesfallfar

##### New Member
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

### 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

 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, 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!!

Thanks for the feedback.

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

Leavesfallfar, Good evening.

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

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

Replies
3
Views
173
Replies
16
Views
1K
Replies
2
Views
148
Replies
11
Views
966
Replies
3
Views
86

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.

### Which adblocker are you using?

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

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