Referencing multiple cells to return information that is contained in the same column

[fmoaveni]

Hello everyone,
I have an excel sheet I have created. I am using Microsoft excel 2013.

I have two sheets on this excel

Data and Pivot are the two sheet names.

On the data sheet I have 12 columns and from these I am making a pivot table on the "Pivot" sheet. Next to that table I have created several cells with formulas that find the lowest start time and the highest start time.

The start times are tied to orders that are being picked within a warehouse.

Here is an example from column A to column L on the DATA Page. You can see there are two pickers picking the same warehouse order.

Warehouse Order	Warehouse Task	Activity Area	Start Date	Start Time	Warehouse Task Status	Dest. Handling Unit	Act.Qty Dest. Alt.Un	Alt. Unit of Measure	Confirmed by	Confirmation Date	Confirmation Time
5000090338	3000535432	A1S0	26/09/2016	13:35:50	C	343357545000211481	2	K01	30001283	26/09/2016	13:39:47
5000090338	3000535478	A1S0	26/09/2016	13:39:48	C	343357545000211481	1	K01	30001283	26/09/2016	13:41:44

<tbody>
</tbody>

5000090338	3000535455	A1S0	26/09/2016	17:21:19	C	343357545000212563	3	K01	30001662	26/09/2016	17:22:50
5000090338	3000535457	A1S0	26/09/2016	17:22:51	C	343357545000212563	2	K01	30001662	26/09/2016	17:23:41

<tbody>
</tbody>

The next sheet has several formulas that are referencing both the pivot table cells on the "pivot" sheet and the "data" sheet. Here is an example of the "pivot" sheet.

			Avg rate Overall		Picker #		AA		Start of 1st Task		Last Task Confirmed		Total Time Per WO		Total Total time cum. All Orders		Total CPH Per WO
Row Labels	Sum of Act.Qty Dest. Alt.Un
30000120	818		174.7241						09:04:14		13:45:08		04:40:54		00:00:00		175
5000090364	818				30000120		A1S0		09:04:14		13:45:08		04:40:54		04:40:54		175
30000132	185		39.3524						17:29:15		19:23:03		01:53:48		12:03:32		98
5000090353	185				30000132		A1S0		17:02:01		19:23:03		02:21:02		04:42:04		79
30000311	578		122.9279						09:19:00		14:01:07		04:42:07		12:03:32		123
5000090347	578				30000311		A1S0		09:19:00		14:01:07		04:42:07		04:42:07		123
30000346	0								16:48:36		16:48:36				12:03:32
5000090340	0				30001456		A1S0		08:51:29		16:48:36		07:57:07		15:54:14		0
30000689	373		24.56012						20:05:32		21:56:07		01:50:35		12:03:32		202
5000090156	373				30000689		A1S0		14:20:30		21:56:07		07:35:37		15:11:14		49
30000886	264		175.1567						08:21:40		09:54:37		01:32:57		12:03:32		170
5000090312	8				30000886		A1S0		08:21:39		08:24:22		00:02:43		01:30:26		177
5000090328	256				30000886		A1S0		08:26:54		09:54:37		01:27:43		01:30:26		175
30001158	1030.5		196.5041						08:23:33		15:05:25		06:41:52		12:03:32		154
5000090311	614.5				30001158		A1S0		08:23:33		11:57:16		03:33:43		05:14:39		173
5000090350	416				30001158		A1S0		13:24:29		15:05:25		01:40:56		05:14:39		247
30001251	714		129.6872						08:56:28		14:21:19		05:24:51		12:03:32		132
5000090335	714				30001251		A1S0		08:50:59		14:21:19		05:30:20		05:30:20		130
30001283	177		14.91154						13:39:47		14:39:37		00:59:50		12:03:32		177
5000090338	177				30001283		A1S0		13:35:50		19:31:56		05:56:06		11:52:12		30
30001333	393		138.9102						09:05:25		11:53:01		02:47:36		12:03:32		141
5000090368	393				30001333		A1S0		09:03:16		11:53:01		02:49:45		02:49:45		139
30001407	249		147.4827						20:56:43		22:35:40		01:38:57		12:03:32		151
5000091821	249				30001407		A1S0		20:54:22		22:35:40		01:41:18		01:41:18		147
30001456	779		48.98173						08:55:10		14:20:44		05:25:34		12:03:32		144
5000090340	779				30001456		A1S0		08:51:29		16:48:36		07:57:07		15:54:14		98
30001457	107		107.3878						21:39:31		22:39:18		00:59:47		12:03:32		107
5000091828	107				30001457		A1S0		21:39:31		22:39:18		00:59:47		00:59:47		107
30001626	455		180.695						08:07:06		10:38:11		02:31:05		12:03:32		181
5000090183	455				30001626		A1S0		08:07:06		10:38:11		02:31:05		02:31:05		181
30001634	138		93.17329						20:41:58		22:18:01		01:36:03		12:03:32		86
5000090158	123				30001634		A1S0		20:41:58		21:50:22		01:08:24		01:28:52		108
5000093083	15				30001634		A1S0		21:57:33		22:18:01		00:20:28		01:28:52		44
30001640	2								14:29:14		14:29:14				12:03:32
5000090156	2				30000689		A1S0		14:20:30		21:56:07		07:35:37		15:11:14		0
30001650	529		195.5638						12:59:46		22:38:03		09:38:17		12:03:32		55
5000092065	371				30001650		A1S0		20:59:05		22:38:03		01:38:58		02:42:18		225
5000091825	158				30001650		A1S0		12:59:46		14:03:06		01:03:20		02:42:18		150
30001662	757		194.9496						17:06:24		23:25:39		06:19:15		12:03:32		120
5000090338	302				30001283		A1S0		13:35:50		19:31:56		05:56:06		11:52:12		51
5000093079	455				30001662		A1S0		19:32:40		23:25:39		03:52:59		03:52:59		117
30001668	172		150.0363						21:37:46		22:43:56		01:06:10		12:03:32		156

<tbody>
</tbody>

5000090338

177

=IFERROR(B23/D23,"")

=IFERROR(VLOOKUP(A23,Data!$1:$1048576,10,0),"")

=IFERROR(VLOOKUP(A23,Data!$1:$1048576,3,0),"")

=IF(A23<5000000000,"",(MIN(IF(Data!A:A=A23,Data!E:E,IF(Data!J:J=A23,Data!L:L,1)))))

=IFERROR(MAX(IF(Data!A:A=A23,Data!L:L,IF(A23<5000000000,"",IF(Data!J:J=A23,Data!L:L,0)))),"")

=IFERROR(IF(M23-K23>0,M23-K23,""),"")

=SUMIF(G:G,G23,O:O)

=IFERROR(IF(O23>0.20833,B23/((O23-$U$3)*24),B23/(O23*24)),"")

<tbody>
</tbody>

These are the pivot table numbers, followed the formulas used in the "Pivot" sheet.

I have put the cells in red that are the main issue here. But if it happens at all it will not give an accurate report. Does anyone know how to find the end time that a picker finished and then the new start time.

Please let me know if you can help.

maybe someone even knows a better way to use a macro to get this information. The formulas really take a while. I dont know how to attach the file on here.

 

[fmoaveni]

Another thing to note is that the min and max formulas contain brackets which allows them to work. otherwise they don't. thanks!