Spreadsheet: 784762d1655943487-find-string-then-find-immediately-below-matched-string-find-the-word-subtotal-then-exampleorderform2
Sheet: timesheet

 |  |  |  | A sheet like this is produced every day by a bespoke system and the number of orders that exist in column B will be <100 however, a single order could consume a few rows to describe, whereas another order could consume many rows if many people have worked on the same order, or if the order has many sub-parts to it. Therefore, the only pivot point from an order number in columb B to the total in column L is finding the word "subtotal" directly underneath where the order number is found in column B and then going across to Column L to pull out that value as highlighted. In column B, the "end of file" marker that can be used is "Total" if that's even required.
 | Orders
 | Created on dd/mm/yyyy at hh:[NAME]:ss
 | Orders
 | For all timeshets
 | Where orders contain P1988, P2046, P2143
 | As below
 | ORDER | SUB PART TO ORDER | WHO WORKED ON ORDER |  | START | END | ESTIMATED TIME | ACTUAL TIME | OT? | COST PER HOUR | TOTAL
 | SALE P1988-Order | Packaging | [NAME] |  | 2021-03-21 00:00:00 | 2021-03-27 00:00:00 | 5 | 0.25 |  | 225 | 56.25
 |  |  |  |  | 2021-02-07 00:00:00 | 2021-02-13 00:00:00 | 5 | 0.25 |  | 225 | 56.25
 |  |  |  |  | 2021-01-17 00:00:00 | 2021-01-23 00:00:00 | 5 | 0.5 |  | 225 | 112.5
 |  |  |  |  | 2020-12-06 00:00:00 | 2020-12-12 00:00:00 | 5 | 1.25 |  | 225 | 281.25
 |  |  |  |  | 2020-11-08 00:00:00 | 2020-11-14 00:00:00 | 5 | 1.5 |  | 225 | 337.5
 |  |  |  |  | 2020-11-01 00:00:00 | 2020-11-07 00:00:00 | 5 | 2 |  | 225 | 450
 |  |  | Subtotal | Sum |  |  | 5 | 5.75 |  | 1350 | 1293.75
 |  |  |  | Count | 6 |  |  |  |  | 0 | 0
 |  | Subtotal |  | Sum |  |  | 5 | 5.75 |  | 1350 | 1293.75
 |  |  |  | Count | 6 |  |  |  |  | 0 | 0
 |  | Courier | [NAME] |  | 2020-12-13 00:00:00 | 2020-12-19 00:00:00 | 15 | 3.75 |  | 225 | 843.75
 |  |  |  |  | 2020-11-22 00:00:00 | 2020-11-28 00:00:00 | 15 | 0.25 |  | 225 | 56.25
 |  |  |  |  | 2020-11-22 00:00:00 | 2020-11-28 00:00:00 | 15 | 0.25 |  | 225 | 56.25
 |  |  |  |  | 2020-11-15 00:00:00 | 2020-11-21 00:00:00 | 15 | 2.25 |  | 225 | 506.25
 |  |  |  |  | 2020-11-08 00:00:00 | 2020-11-14 00:00:00 | 15 | 6 |  | 225 | 1350
 |  |  | Subtotal | Sum |  |  | 15 | 12.5 |  | 1125 | 2812.5
 |  |  |  | Count | 5 |  |  |  |  | 0 | 0
 |  | Subtotal |  | Sum |  |  | 15 | 12.5 |  | 1125 | 2812.5
 |  |  |  | Count | 5 |  |  |  |  | 0 | 0
 |  | Rebuild | [NAME] |  | 2021-05-02 00:00:00 | 2021-05-08 00:00:00 | 55 | 0.25 |  | 225 | 56.25
 |  |  |  |  | 2021-04-25 00:00:00 | 2021-05-01 00:00:00 | 55 | 0.25 |  | 225 | 56.25
 |  |  |  |  | 2021-04-18 00:00:00 | 2021-04-24 00:00:00 | 55 | 3.5 |  | 225 | 787.5
 |  |  |  |  | 2021-03-28 00:00:00 | 2021-04-03 00:00:00 | 55 | 0.25 |  | 225 | 56.25
 |  |  |  |  | 2021-03-21 00:00:00 | 2021-03-27 00:00:00 | 55 | 0.5 |  | 225 | 112.5
 |  |  |  |  | 2021-02-07 00:00:00 | 2021-02-13 00:00:00 | 55 | 1.75 |  | 225 | 393.75
 |  |  |  |  | 2021-01-17 00:00:00 | 2021-01-23 00:00:00 | 55 | 4 |  | 225 | 900
 |  |  |  |  | 2021-01-10 00:00:00 | 2021-01-16 00:00:00 | 55 | 11 |  | 225 | 2475
 |  |  |  |  | 2020-12-20 00:00:00 | 2020-12-26 00:00:00 | 55 | 5 |  | 225 | 1125
 |  |  |  |  | 2020-12-13 00:00:00 | 2020-12-19 00:00:00 | 55 | 10.5 |  | 225 | 2362.5
 |  |  |  |  | 2020-12-06 00:00:00 | 2020-12-12 00:00:00 | 55 | 13.75 |  | 225 | 3093.75
 |  |  | Subtotal | Sum |  |  | 55 | 50.75 |  | 2475 | 11418.75
 |  |  |  | Count | 11 |  |  |  |  | 0 | 0
 |  |  | [NAME] |  | 2020-12-06 00:00:00 | 2020-12-12 00:00:00 | 55 | 1 |  | 175 | 175
 |  |  | Subtotal | Sum |  |  | 55 | 1 |  | 175 | 175
 |  |  |  | Count | 1 |  |  |  |  | 0 | 0
 |  | Subtotal |  | Sum |  |  | 55 | 51.75 |  | 2650 | 11593.75
 |  |  |  | Count | 12 |  |  |  |  | 0 | 0
 | Subtotal |  |  | Sum |  |  | 75 | 70 |  | 3775 | 15700
 |  |  |  | Count | 23 |  |  |  |  | 0 | 0
 | SALE P2046-Order | Plant costs | [NAME] |  | 2021-02-21 00:00:00 | 2021-02-27 00:00:00 | 10 | 2 |  | 275 | 550
 |  |  | Subtotal | Sum |  |  | 10 | 2 |  | 275 | 550
 |  |  |  | Count | 1 |  |  |  |  | 0 | 0
 |  | Subtotal |  | Sum |  |  | 10 | 2 |  | 275 | 550
 |  |  |  | Count | 1 |  |  |  |  | 0 | 0
 | Subtotal |  |  | Sum |  |  | 10 | 2 |  | 275 | 550
 |  |  |  | Count | 1 |  |  |  |  | 0 | 0
 | SALE P2143 (AU)-Order | Maintenance | [NAME] |  | 2021-02-14 00:00:00 | 2021-02-20 00:00:00 | 80 | 2 |  | 175 | 350
 |  |  | Subtotal | Sum |  |  | 80 | 2 |  | 175 | 350
 |  |  |  | Count | 1 |  |  |  |  | 0 | 0
 |  |  | [NAME] |  | 2021-02-21 00:00:00 | 2021-02-27 00:00:00 | 80 | 1 |  | 275 | 275
 |  |  | Subtotal | Sum |  |  | 80 | 1 |  | 275 | 275
 |  |  |  | Count | 1 |  |  |  |  | 0 | 0
 |  |  | [NAME] |  | 2021-02-21 00:00:00 | 2021-02-27 00:00:00 | 80 | 1.5 |  | 275 | 412.5
 |  |  | Subtotal | Sum |  |  | 80 | 1.5 |  | 275 | 412.5
 |  |  |  | Count | 1 |  |  |  |  | 0 | 0
 |  | Subtotal |  | Sum |  |  | 80 | 4.5 |  | 725 | 1037.5
 |  |  |  | Count | 3 |  |  |  |  | 0 | 0
 |  | Fixing new parts | [NAME] |  | 2021-02-21 00:00:00 | 2021-02-27 00:00:00 | 10 | 1.75 |  | 275 | 481.25
 |  |  |  |  | 2021-02-14 00:00:00 | 2021-02-20 00:00:00 | 10 | 3.25 |  | 275 | 893.75
 |  |  | Subtotal | Sum |  |  | 10 | 5 |  | 550 | 1375
 |  |  |  | Count | 2 |  |  |  |  | 0 | 0
 |  | Subtotal |  | Sum |  |  | 10 | 5 |  | 550 | 1375
 |  |  |  | Count | 2 |  |  |  |  | 0 | 0
 |  | Upgrading | [NAME] |  | 2021-02-07 00:00:00 | 2021-02-13 00:00:00 | 12 | 1 | T/Half | 412.5 | 412.5
 |  |  | Subtotal | Sum |  |  | 12 | 1 |  | 412.5 | 412.5
 |  |  |  | Count | 1 |  |  |  |  | 0 | 0
 |  | Subtotal |  | Sum |  |  | 12 | 1 |  | 412.5 | 412.5
 |  |  |  | Count | 1 |  |  |  |  | 0 | 0
 |  | Couriers | [NAME] |  | 2021-02-07 00:00:00 | 2021-02-13 00:00:00 | 54 | 4.5 |  | 275 | 1237.5
 |  |  |  |  | 2021-01-31 00:00:00 | 2021-02-06 00:00:00 | 54 | 6.5 |  | 275 | 1787.5
 |  |  |  |  | 2021-01-31 00:00:00 | 2021-02-06 00:00:00 | 54 | 2 | Double | 550 | 1100
 |  |  |  |  | 2021-01-24 00:00:00 | 2021-01-30 00:00:00 | 54 | 4 |  | 275 | 1100
 |  |  |  |  | 2021-01-17 00:00:00 | 2021-01-23 00:00:00 | 54 | 3 |  | 275 | 825
 |  |  | Subtotal | Sum |  |  | 54 | 20 |  | 1650 | 6050
 |  |  |  | Count | 5 |  |  |  |  | 0 | 0
 |  |  | [NAME] |  | 2021-02-14 00:00:00 | 2021-02-20 00:00:00 | 54 | 2.5 |  | 275 | 687.5
 |  |  |  |  | 2021-02-07 00:00:00 | 2021-02-13 00:00:00 | 54 | 2 |  | 275 | 550
 |  |  |  |  | 2021-01-31 00:00:00 | 2021-02-06 00:00:00 | 54 | 3.75 |  | 275 | 1031.25
 |  |  |  |  | 2021-01-31 00:00:00 | 2021-02-06 00:00:00 | 54 | 1.25 |  | 275 | 343.75
 |  |  |  |  | 2021-01-24 00:00:00 | 2021-01-30 00:00:00 | 54 | 1.5 |  | 275 | 412.5
 |  |  | Subtotal | Sum |  |  | 54 | 11 |  | 1375 | 3025
 |  |  |  | Count | 5 |  |  |  |  | 0 | 0
 |  | Subtotal |  | Sum |  |  | 54 | 31 |  | 3025 | 9075
 |  |  |  | Count | 10 |  |  |  |  | 0 | 0
 | Subtotal |  |  | Sum |  |  | 156 | 41.5 |  | 4162.5 | 11900
 |  |  |  | Count | 16 |  |  |  |  |  | 0
 | Total |  |  | Sum |  |  |  |  |  |  | 28150
 |  |  |  | Count