Maybe this:
Array formula - use Ctrl+Shift+Enter and not only Enter to enter the formula
Code:
=IFERROR(INDEX('Data Dump'!$A$2:$L$8,
SMALL(IF('Data Dump'!$D$2:$D$8=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,99),ROW('Data Dump'!$D$2:$D$8)-ROW('Data Dump'!$D$2)+1),ROWS(A$2:A2)),
COLUMNS($A2:A2)),"")
PS: use the same formula in each sheet (Purple,LOC and Two-Ten)
Markmzz
Hi Rcirone,
Did you try my formula of the post #3 (the above formula)?
If yes, what you got?
Here is the result with my sample workbook
[TABLE="width: 482"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent"]
Agent Name
[/TD]
[TD="class: xl63, width: 31, bgcolor: transparent"]
E#
[/TD]
[TD="class: xl63, width: 87, bgcolor: transparent"]
Supervisor Name
[/TD]
[TD="class: xl63, width: 47, bgcolor: transparent"]
Program
[/TD]
[TD="class: xl63, width: 49, bgcolor: transparent"]
Phone
[/TD]
[TD="class: xl63, width: 28, bgcolor: transparent"]
True
[/TD]
[TD="class: xl63, width: 49, bgcolor: transparent"]
Total
[/TD]
[TD="class: xl63, width: 58, bgcolor: transparent"]
Productive
[/TD]
[TD="class: xl63, width: 42, bgcolor: transparent"]
Lunch
[/TD]
[TD="class: xl63, width: 42, bgcolor: transparent"]
Non-
[/TD]
[TD="class: xl63, width: 42, bgcolor: transparent"]
Talk
[/TD]
[TD="class: xl63, width: 42, bgcolor: transparent"]
Calls
[/TD]
[TD="class: xl64, width: 58, bgcolor: transparent"]
Data Dump
[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Name01
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
2608
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Name10
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Purple
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
08:33:39
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
176
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
23:11:20
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data01
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data07
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data13
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data19
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data25
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Name02
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
2235
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Name11
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Two-Ten
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
23:34:11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
278
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
13:58:13
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data02
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data08
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data14
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data20
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data26
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Name03
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
8618
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Name12
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Two-Ten
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
21:43:06
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
275
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
19:51:27
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data03
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data09
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data15
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data21
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data27
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Name04
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
2146
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Name13
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Two-Ten
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
23:02:37
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
162
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
06:51:07
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data04
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data10
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data16
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data22
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data28
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Name05
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
8976
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Name14
[/TD]
[TD="class: xl63, bgcolor: transparent"]
LOC
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
12:03:53
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
282
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
02:16:39
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data05
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data11
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data17
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data23
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data29
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Name06
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
8239
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Name15
[/TD]
[TD="class: xl63, bgcolor: transparent"]
LOC
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
17:14:44
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
202
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
03:37:44
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data06
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data12
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data18
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data24
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data30
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Name07
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
8425
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Name16
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Two-Ten
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
01:37:11
[/TD]
[TD="class: xl63, bgcolor: transparent, align: right"]
299
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]
05:15:56
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data07
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data13
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data19
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data25
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Data31
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
*
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Agent Name
[/TD]
[TD="class: xl63, bgcolor: transparent"]
E#
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Supervisor Name
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Program
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Phone
[/TD]
[TD="class: xl63, bgcolor: transparent"]
True
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Total
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Productive
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Lunch
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Non-
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Talk
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Calls
[/TD]
[TD="class: xl64, bgcolor: transparent"]
Two-Ten
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]
Name02
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
2235
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Name11
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Two-Ten
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
23:34:11
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
278
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
13:58:13
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data02
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data08
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data14
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data20
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data26
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]
Name03
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
8618
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Name12
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Two-Ten
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
21:43:06
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
275
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
19:51:27
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data03
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data09
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data15
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data21
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data27
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]
Name04
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
2146
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Name13
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Two-Ten
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
23:02:37
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
162
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
06:51:07
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data04
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data10
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data16
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data22
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data28
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]
Name07
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
8425
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Name16
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Two-Ten
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
01:37:11
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
299
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
05:15:56
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data07
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data13
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data19
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data25
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data31
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
*
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Agent Name
[/TD]
[TD="class: xl63, bgcolor: transparent"]
E#
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Supervisor Name
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Program
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Phone
[/TD]
[TD="class: xl63, bgcolor: transparent"]
True
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Total
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Productive
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Lunch
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Non-
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Talk
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Calls
[/TD]
[TD="class: xl64, bgcolor: transparent"]
LOC
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]
Name05
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
8976
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Name14
[/TD]
[TD="class: xl66, bgcolor: yellow"]
LOC
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
12:03:53
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
282
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
02:16:39
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data05
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data11
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data17
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data23
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data29
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]
Name06
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
8239
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Name15
[/TD]
[TD="class: xl66, bgcolor: yellow"]
LOC
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
17:14:44
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
202
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
03:37:44
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data06
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data12
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data18
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data24
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data30
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
*
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
Agent Name
[/TD]
[TD="class: xl63, bgcolor: transparent"]
E#
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Supervisor Name
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Program
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Phone
[/TD]
[TD="class: xl63, bgcolor: transparent"]
True
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Total
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Productive
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Lunch
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Non-
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Talk
[/TD]
[TD="class: xl63, bgcolor: transparent"]
Calls
[/TD]
[TD="class: xl64, bgcolor: transparent"]
Purple
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: yellow"]
Name01
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
2608
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Name10
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Purple
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
08:33:39
[/TD]
[TD="class: xl66, bgcolor: yellow, align: right"]
176
[/TD]
[TD="class: xl67, bgcolor: yellow, align: right"]
23:11:20
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data01
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data07
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data13
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data19
[/TD]
[TD="class: xl66, bgcolor: yellow"]
Data25
[/TD]
[TD="class: xl63, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
************
[/TD]
[TD="class: xl63, bgcolor: transparent"]
*****
[/TD]
[TD="class: xl63, bgcolor: transparent"]
*****************
[/TD]
[TD="class: xl63, bgcolor: transparent"]
********
[/TD]
[TD="class: xl63, bgcolor: transparent"]
*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]
*****
[/TD]
[TD="class: xl63, bgcolor: transparent"]
*********
[/TD]
[TD="class: xl63, bgcolor: transparent"]
***********
[/TD]
[TD="class: xl63, bgcolor: transparent"]
********
[/TD]
[TD="class: xl63, bgcolor: transparent"]
*******
[/TD]
[TD="class: xl63, bgcolor: transparent"]
*******
[/TD]
[TD="class: xl63, bgcolor: transparent"]
*******
[/TD]
[TD="class: xl63, bgcolor: transparent"]
***********
[/TD]
[/TR]
</tbody>[/TABLE]
Markmzz