Jim Mc
07-21-2017, 10:13 AM
Hi folks,
Thank you for letting me join and taking the time to look over my query.
I have a workbook that consists of two sheets, sheet 1 collects the data, sheet 2 compiles it. I hopefully have included some test data below of each sheet
Sheet 1
Date
Location
Name
Grade
Level
Time on Business (mins)
Travel Time (mins)
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
Sheet 2
total
No. Cases
time at court
DATE
Daily savings total
MICHAEL
01.01.2017
02.01.2017
03.01.2017
£ 1,360.00
9
215
04.01.2017
05.01.2017
06.01.2017
07.01.2017
08.01.2017
Column letters are A, B, C etc. in both cases and rows are 1, 2, 3 etc.
Column D on sheet 2 totals all the values from another part of the real sheet and is not the problem.
what I want to do is :
Total all the cases covered by Michael on 03.01.2017 from sheet 1 and show that total in E5 on sheet 2 -in this example 9 cases.
Show his time at business on 03.01.2017 from sheet 1 in F5 on sheet 2 - not the total of 9*215 just as shown in the example 215 [I need 215 next to each record on sheet 1 for elsewhere in the workbook]
What formula do I need for E5 and F5 in order to calculate this? I have tried several combinations of INDEX, MATCH, LOOKUP and even praying for guidance but keep getting errors in my formula.
Please help as I have been working on this for several days and getting quite frustrated with it. I am not back at my desk until Monday so will be unable to respond until then.
Cheers,
Jim
Thank you for letting me join and taking the time to look over my query.
I have a workbook that consists of two sheets, sheet 1 collects the data, sheet 2 compiles it. I hopefully have included some test data below of each sheet
Sheet 1
Date
Location
Name
Grade
Level
Time on Business (mins)
Travel Time (mins)
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
03.01.2017
LIVERPOOL
MICHAEL
010407
3
215
20
Sheet 2
total
No. Cases
time at court
DATE
Daily savings total
MICHAEL
01.01.2017
02.01.2017
03.01.2017
£ 1,360.00
9
215
04.01.2017
05.01.2017
06.01.2017
07.01.2017
08.01.2017
Column letters are A, B, C etc. in both cases and rows are 1, 2, 3 etc.
Column D on sheet 2 totals all the values from another part of the real sheet and is not the problem.
what I want to do is :
Total all the cases covered by Michael on 03.01.2017 from sheet 1 and show that total in E5 on sheet 2 -in this example 9 cases.
Show his time at business on 03.01.2017 from sheet 1 in F5 on sheet 2 - not the total of 9*215 just as shown in the example 215 [I need 215 next to each record on sheet 1 for elsewhere in the workbook]
What formula do I need for E5 and F5 in order to calculate this? I have tried several combinations of INDEX, MATCH, LOOKUP and even praying for guidance but keep getting errors in my formula.
Please help as I have been working on this for several days and getting quite frustrated with it. I am not back at my desk until Monday so will be unable to respond until then.
Cheers,
Jim