Could someone help me please
I am extracting data from a d/b, and have 4 columns of data:
Empno
Date
Timecode
Duration
On a daily basis there are multiple entries for each employee, for date, with different timecodes and differing durations.
I have built a privot table which produces a table by employee (rows) for each timecode (coulmns) summing the durations.
I then use vlookup in another spreadsheet to extract the relevant data to compute certain rates etc.
Timecodes are a fixed number, but may not all be used in a month. This means that the Pivot table will be dynamic, new columns being added as timecodes are used during the month e.g. Day 1 Timecodes 1,2,3,10,11,12 maybe used. On Day 2 Timecodes 1,2,3,6,7,8,10,11,12 may also be used and so on.
This doesn't help the end spreadsheet as the VLookups will have to be continually updated to keep pace with the changes in use of Timecodes.
I hope this makes sense so far.
What I'd like to do is have a Vlookup that looks at the original Table, and will sum all Durations for a specific Timecode for each Empno.
I don't know how to develop the formula to get this to work.
Can anyone advise what this should be.
Many thanks
Jeremy
I am extracting data from a d/b, and have 4 columns of data:
Empno
Date
Timecode
Duration
On a daily basis there are multiple entries for each employee, for date, with different timecodes and differing durations.
I have built a privot table which produces a table by employee (rows) for each timecode (coulmns) summing the durations.
I then use vlookup in another spreadsheet to extract the relevant data to compute certain rates etc.
Timecodes are a fixed number, but may not all be used in a month. This means that the Pivot table will be dynamic, new columns being added as timecodes are used during the month e.g. Day 1 Timecodes 1,2,3,10,11,12 maybe used. On Day 2 Timecodes 1,2,3,6,7,8,10,11,12 may also be used and so on.
This doesn't help the end spreadsheet as the VLookups will have to be continually updated to keep pace with the changes in use of Timecodes.
I hope this makes sense so far.
What I'd like to do is have a Vlookup that looks at the original Table, and will sum all Durations for a specific Timecode for each Empno.
I don't know how to develop the formula to get this to work.
Can anyone advise what this should be.
Many thanks
Jeremy