vlookup & indirect to display metric data

DarrenK

New Member
Joined
Aug 5, 2017
Messages
33
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows
So the %AVG on each day sheet has only one value on the sheet, not different for each Agent?
The values to the right of %AVG are a single average for all Agents calculated for the heading? e.g. Wrap Time %AVG setting in your example is 59:53
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

DarrenK

New Member
Joined
Aug 5, 2017
Messages
33
KLMNOPQRS
%AVG%AVG%AVG%AVG%AVG%AVG
0:00:000:00:000:00:000:00:000:00:000:00:00
50% above50% above100% above400% above13% above13% above
HANDLEWRAPHOLDNOT RDYLUNCHBREAK
 

DarrenK

New Member
Joined
Aug 5, 2017
Messages
33
Sorry for the extra image. Kept refreshing but it never showed me your response. It's not different per agent. Just different per metric.

Below is how I had it originally. I would use the calculation in columns K, N, O, P, R and S (of DATED TABs) to populate a trend line (setup in columns E, G, I, K of AGENT LOOKUP tab) that would fluctuate based on the daily %AVG calculation. It would be imposed on the graph so that way no matter which agent was chosen from the dropdown on AGENT LOOKUP, the trend line would remain the same.

J K N O P R S
1607641738535.png


I thought maybe I would add the %AVG above each individual metric daily average to make lookup easier since I wasn't able to get the above to work correctly. But I think below is even worse.
K L M N O P R S
1607641705616.png



1607641658818.png
 

DarrenK

New Member
Joined
Aug 5, 2017
Messages
33
ok the letters didn't space right. K is above 50% above Handle, N is above 50% above Wrap, etc. J is technically above the %AVG that appears in the first screenshot showing the daily averages.
 

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows
Here's my final attempt.

I've assumed the Agent Lkup %avg columns are the selected agent value for the column on its left as a percentage of the retrieved value.

Here's my two test daily tabs:

DarrenK3.xlsx
BJKNOPRS
1AVG IB HandleWrap Time*Hold Time*Not Ready Time*Lunch Time*Break Time
2Jill3:574:301:221:351:321:49
3Sarah5:123:522:302:531:181:00
4Bob5:045:371:171:281:541:41
5Bert12:146:431:462:032:172:21
6
7
8
9%AVG6:365:101:442:001:451:43
12-01
Cell Formulas
RangeFormula
K9,R9:S9,N9:P9K9=AVERAGE(K$2:K$7)


DarrenK3.xlsx
BJKNOPRS
1AVG IB HandleWrap Time*Hold Time*Not Ready Time*Lunch Time*Break Time
2Jill3:263:554:241:120:440:55
3Sarah2:303:224:332:111:091:02
4Bob4:244:535:221:071:111:22
5Bert5:225:506:191:330:441:12
6
7
8
9
10
11
12%AVG3:554:305:091:300:571:07
12-02
Cell Formulas
RangeFormula
K12,R12:S12,N12:P12K12=AVERAGE(K$2:K$10)


And here's the Agent Lkup

DarrenK3.xlsx
BCDEFGHIJKLMNOPQ
1Agents
2AVG IB Handle%avgWrap Time%avg*Hold Time%avg*Not Ready Time%avg*Lunch Time%avg*Break Time%avgBob
312-015:0476.60%5:37108.43%1:1773.83%1:2873.83%1:54108.43%1:4198.73%Sarah
4Bob12-024:24112.13%4:53108.43%5:22103.95%1:0773.83%1:11124.56%1:22120.68%Jill
512-03            Bert
612-04            
712-05            
Agent Lookup
Cell Formulas
RangeFormula
D3:O7D3=IFERROR(IF(D$2="%avg",C3/VLOOKUP("%AVG",INDIRECT("'"&TEXT($C3,"mm-dd")&"'!$J$2:$Z$99"),MATCH(C$2,INDIRECT("'"&TEXT($C3,"mm-dd")&"'!$A$1:$Z$1"),0)-COLUMN($J$1)+1,0),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:C7C4=IF(OR(C3=EOMONTH($C$3,0),C3=""),"",C3+1)
Cells with Data Validation
CellAllowCriteria
B4List=$Q$2:$Q$5
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,249
Messages
5,600,537
Members
414,386
Latest member
PARAMATHMA SENTHILNATHAN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top