vlookup & indirect to display metric data

[DarrenK]

I am absolutely clueless on the formula. I have a sheet titled Agent Lookup. I have other sheet tabs labeled with the date of each report (12-01, 12-02, 12-03, 12-04). In Agent Lookup cell B4 I have a data validation list of all agent names. In C3 I have 12-01, which references the data from that date going across but also references the tab '12-01'. And then C4:Cxx will be the rest of the dates of the month.

In D3:I3, I want to populate the data from each sheet based on the header (Handle Time, Wrap Time, Hold Time, Lunch Time, Break Time, etc).

=VLOOKUP(B4,INDIRECT("'" & $C$3 & "'!"),1,TRUE) I know it's completely incorrect but an having trouble understanding how to reference the other sheets in the formula. I want to take the Handle Time for 12-1 (column K in '12-01' sheet), cross reference the agent's name in 'Agent Lookup' B4, and have it locate data in the cell in column K which corresponds to the row in which the agent name appears.

If Joe Smith's name is in B4, it would pull data from K11 because Joe's name appears in row 11, the data of course being in column K. What am I missing in my formula?

Thank you in advance

 

[Toadstool]

Hi DarrenK,

It was difficult to follow parts of your requirement so I've made some assumptions, such as the tab name is a mm-dd but the date in C3 is month day and year but formatted as mm-dd. I'm assuming all dated tabs have headings in the same place but understand the staff names may be in a different sequence.

I've put IFERROR checking in but if you need to trap when a tab name doesn't exist (#REF error) or a staff name is not present (#N/A) then you'll need to add further checks.

I've just created 2 test tabs, 12-01 and 12-02

DarrenK.xlsx
	J	K	L	M	N	O
1		Handle Time	Wrap Time	Hold Time	Lunch Time	Break Time
2	Bert	5:22	5:50	6:19	0:44	1:12
3	Bob	4:24	4:53	5:22	1:11	1:22
4	Jill	3:26	3:55	4:24	0:44	0:55
5	Sarah	3:46	2:58	3:26	0:32	0:48
12-01

DarrenK.xlsx
	J	K	L	M	N	O
1		Handle Time	Wrap Time	Hold Time	Lunch Time	Break Time
2	Jill	3:26	3:55	4:24	0:44	0:55
3	Sarah	2:12	3:22	4:33	1:09	1:02
4	Bob	4:24	4:53	5:22	1:11	1:22
5	Bert	5:22	5:50	6:19	0:44	1:12
12-02

Here's the Agent Lookup tab:

DarrenK.xlsx
	B	C	D	E	F	G	H	I	J	K
1										Agents
2			Handle Time	Wrap Time	Hold Time	Lunch Time	Break Time			Bob
3		12-01	3:46	2:58	3:26	0:32	0:48			Sarah
4	Sarah	12-02	2:12	3:22	4:33	1:09	1:02			Jill
5		12-03								Bert
6		12-04
7		12-05
8		12-06
Agent Lookup

Cell Formulas
Range	Formula
D3:H8	D3	=IFERROR(VLOOKUP($B$4,INDIRECT("'"&TEXT($C3,"mm-dd")&"'!$J$2:$O$99"),COLUMNS($C3:D3),0),"")
C4:C8	C4	=IF(OR(C3=EOMONTH($C$3,0),C3=""),"",C3+1)

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=$K$2:$K$5

 

[DarrenK]

Hello Toadstool,

I appreciate the assistance. I added the formulas exactly. The date formula populated column c exactly as it should, all the way down to 12-31. You were correct in the tabs being marked mm-dd since the file would only apply to current year. And the dates in column C are also displayed mm-dd just like you did. I have the data validation tab setup correctly.

The only issue is that the IFERROR/VLOOKUP/INDIRECT formula doesn't populate anything. I looked through it and your $J$2:$O$99 appears to reference the columns in which the names/raw metric data appear in the dated tabs? I adjusted the formula so that it goes from column B through column S (raw data has it setup that way). Column B in the raw data has the names listed. K - handle, N-wrap, O-hold, r-lunch and s-break. There are of course columns that are unnecessary between B and S though but I don't think that should matter as it's just referencing the header names, correct?

After making the adjustment, and formatting the cells for Time - hh:mm:ss, every column is now displaying 00:00:00 except for break time. Which generates a time that appears nowhere in the raw data.

Thoughts?

 

[Toadstool]

Hello DarrenK,

Please format all times as Custom, [h]:mm

It's difficult to follow so posting using XL2BB would help, or a screenshot of the Agent Lookup and a sample date tab so I can make sure there's no row/column issues.

Here's another version which requires the headings (Handle, Wrap, Hold, Lunch, Break) to be identical and on row 1 of each date sheet but can then calculate the correct column (as they're staggered).

DarrenK2.xlsx
	B									K			N	O			R	S
1										Handle			Wrap	Hold			Lunch	Break
2	Bert									5:22			5:50	6:19			0:44	1:12
3	Bob									4:24			4:53	5:22			1:11	1:22
4	Jill									3:26			3:55	4:24			0:44	0:55
5	Sarah									3:46			2:58	3:26			0:32	0:48
12-01

DarrenK2.xlsx
	B									K			N	O			R	S
1										Handle			Wrap	Hold			Lunch	Break
2	Jill									3:26			3:55	4:24			0:44	0:55
3	Sarah									2:12			3:22	4:33			1:09	1:02
4	Bob									4:24			4:53	5:22			1:11	1:22
5	Bert									5:22			5:50	6:19			0:44	1:12
12-02

DarrenK2.xlsx
	B	C	D	E	F	G	H	I	J	K
1										Agents
2			Handle	Wrap	Hold	Lunch	Break			Bob
3		12-01	3:46	2:58	3:26	0:32	0:48			Sarah
4	Sarah	12-02	2:12	3:22	4:33	1:09	1:02			Jill
5		12-03								Bert
6		12-04
7		12-05
8		12-06
9		12-07
10		12-08
Agent Lookup

Cell Formulas
Range	Formula
D3:H10	D3	=IFERROR(VLOOKUP($B$4,INDIRECT("'"&TEXT($C3,"mm-dd")&"'!$B$2:$Z$99"),MATCH(D$2,INDIRECT("'"&TEXT($C3,"mm-dd")&"'!$A$1:$Z$1"),0)-1,0),"")
C4:C10	C4	=IF(OR(C3=EOMONTH($C$3,0),C3=""),"",C3+1)

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=$K$2:$K$5

 

[DarrenK]

That did it!!

I re-formatted the times per your suggestion and used the newer D3 formula and it pulled everything exactly from the raw data columns perfectly. It's a work system so I attempted to install the add-in but will have to work on getting it to work.

Thank you again for your assistance!

 

[Toadstool]

You're welcome! Glad to help.

 

[DarrenK]

Hello Toadstool,

I pasted the formula as is and It didn't work because J1 has a header for another metric that I am not tracking and that I should not remove. How do we tweak the =IFERROR(VLOOKUP($B$4,INDIRECT("'"&TEXT($C3,"mm-dd")&"'!$B$2:$Z$99"),MATCH(F$2,INDIRECT("'"&TEXT($C3,"mm-dd")&"'!$A$1:$Z$1"),0)-1,0),"") formula in E3 so that it locates the %AVG anywhere in Column J?

To specify, the %AVG will always be in Column J left of the rows I am tracking but never in J1. How do we make the formula work for that for easy dragging?

 

[Toadstool]

Hello DarrenK,

I am perplexed. You messaged me that you wanted to pull "%AVG" into E3 and that on each day sheet "I have placed the '%AVG' lookup value in column J.". I answered there's no formula change, you just need to match the headings and the formula looks in row 1 on each day sheet for a match.

Now you're saying "the %AVG will always be in Column J left of the rows I am tracking but never in J1" which I'm not following. You'll need to provide a sample of your Agent Lookup and one of the day sheets to clarify. XL2BB would be preferred but even images would help

 

[DarrenK]

J

K

L

M

N

O

P

Q

R

S

T

AVG IB Wrap

AVG IB Handle

AVG OB TT

*Utilization

Wrap Time

*Hold Time

*Not Ready Time

*Approved RC Time

*Lunch Time

*Break Time

App Use of OB

Outbound

0:01:47

0:08:17

0:03:47

88.3%

0:39:55

0:26:21

0:08:15

1:18:09

0:25:06

0:25:38

#DIV/0!

0:16:04

0:35:00

0:36:00

VARIABLE

%AVG

0:12:26

0:59:53

0:52:43

0:33:00

0:35:00

0:36:00

ROW

50% above

50% above

100% above

400% above

13% above

13% above

HANDLE

WRAP

HOLD

NOT RDY

LUNCH

BREAK

 

[DarrenK]

The %AVG will appears always in Column J but not always the same row. Based on the metric I am designing a trendline for, it will use a specific column data (handle will use the 50% value above the average handle time to show if the agent hit the mark or not on the chart.

C	D	E	F	G	H	I	J
Date	AVG IB Handle	%avg	Wrap Time	%avg	*Hold Time	%avg	*Not Ready Time
12-01	0:05:12​	0:10:03​	0:10:03​		0:06:27​		0:58:13​
12-02		0:12:26​

This is my AGENT LOOKUP tab. Sorry the previous post was from one of the date tabs. When I pasted the formula in E3 (1:10:03), it generated the data from F3. I am looking to instead, have it pull from whatever row the %AVG item is listed in. So if I wanted the %AVG for Handle time, it would use column J to find the referenced %AVG lookup value. And then move over to column K because that is the column that would have the calculation (time directly above the 50% above)