-2

I have a google sheet that gets filled via a google form.

Time stamps are created every time a bar code (work order number) is scanned. The work order number is in the first column. The 4 unique time stamp fields below are populated in the 2nd column from the google form.

  1. Setup start
  2. Setup finish
  3. Production start
  4. Production finish

The time stamp is created in the 3rd column.

I am trying to do conditional formatting where the total setup time and production time are calculated but they are tied to their respective work order number.

time stamp functionality

The difficulty is that the timestamp values all fall into one vertical column. I don't want a mix up of timestamp values with different work order numbers. The work order numbers along with the 4 unique time stamp values may be input at various times so the formula can't be order specific.

Is there a way to do this? Thanks!

Below is an example link of the spreadsheet I have: https://drive.google.com/open?id=1YA86jGq_jMsx-wKe19TnZZyf9F4aW6_kUIbrz8hkLJI

knightridar
  • 1
  • 1
  • 1
  • 3

1 Answers1

0

Make a pivot table of the data from the form, then use simple formulas adjacent to the new pivot to get the results you are trying to get. Example Image

warbirdn
  • 83
  • 6
  • wow thanks! this is just what I was looking for. i was trying to do it the harder way on the second spreadsheet i.e. =index(PT!C2:C,MATCH("Setup Start",PT!B2:B,0)) – knightridar Nov 01 '17 at 23:10
  • there is one thing though. how did you manage to add the qty into the pivot table. i did notice that the pivot table is dynamic but how would I get quantities to match up with the respective work order number? – knightridar Nov 01 '17 at 23:36
  • With a VLOOKUP that is searching for Work Order number. =VLOOKUP(A2,PT!A1:D11,4) – warbirdn Nov 02 '17 at 00:02
  • thanks that worked. =VLOOKUP(A3,PT!$A$2:$D,4). I used this to work with sheet format. – knightridar Nov 02 '17 at 17:09
  • I have a bit of an issue. Since the pivot table is dynamic and new work order numbers are added, the pivot table rearranges itself and the VLOOKUP function doesn't work properly. I am trying to use: =INDEX(PT!D2:D,MATCH($A$4,PT!A2:A)) but it still references the wrong cell – knightridar Nov 02 '17 at 19:48
  • I was able to figure out the quantity problem. I made a separate pivot table including just the work order number and quantities. I can do a VLOOKUP function on that and get the right results even after the spreadsheet gets new data inputted into it. – knightridar Nov 02 '17 at 23:46