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