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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows
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
JKLMNO
1Handle TimeWrap TimeHold TimeLunch TimeBreak Time
2Bert5:225:506:190:441:12
3Bob4:244:535:221:111:22
4Jill3:263:554:240:440:55
5Sarah3:462:583:260:320:48
12-01


DarrenK.xlsx
JKLMNO
1Handle TimeWrap TimeHold TimeLunch TimeBreak Time
2Jill3:263:554:240:440:55
3Sarah2:123:224:331:091:02
4Bob4:244:535:221:111:22
5Bert5:225:506:190:441:12
12-02


Here's the Agent Lookup tab:

DarrenK.xlsx
BCDEFGHIJK
1Agents
2Handle TimeWrap TimeHold TimeLunch TimeBreak TimeBob
312-013:462:583:260:320:48Sarah
4Sarah12-022:123:224:331:091:02Jill
512-03     Bert
612-04     
712-05     
812-06     
Agent Lookup
Cell Formulas
RangeFormula
D3:H8D3=IFERROR(VLOOKUP($B$4,INDIRECT("'"&TEXT($C3,"mm-dd")&"'!$J$2:$O$99"),COLUMNS($C3:D3),0),"")
C4:C8C4=IF(OR(C3=EOMONTH($C$3,0),C3=""),"",C3+1)
Cells with Data Validation
CellAllowCriteria
B4List=$K$2:$K$5
 

DarrenK

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

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows
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
BKNORS
1HandleWrapHoldLunchBreak
2Bert5:225:506:190:441:12
3Bob4:244:535:221:111:22
4Jill3:263:554:240:440:55
5Sarah3:462:583:260:320:48
12-01


DarrenK2.xlsx
BKNORS
1HandleWrapHoldLunchBreak
2Jill3:263:554:240:440:55
3Sarah2:123:224:331:091:02
4Bob4:244:535:221:111:22
5Bert5:225:506:190:441:12
12-02


DarrenK2.xlsx
BCDEFGHIJK
1Agents
2HandleWrapHoldLunchBreakBob
312-013:462:583:260:320:48Sarah
4Sarah12-022:123:224:331:091:02Jill
512-03     Bert
612-04     
712-05     
812-06     
912-07     
1012-08     
Agent Lookup
Cell Formulas
RangeFormula
D3:H10D3=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:C10C4=IF(OR(C3=EOMONTH($C$3,0),C3=""),"",C3+1)
Cells with Data Validation
CellAllowCriteria
B4List=$K$2:$K$5
 

DarrenK

New Member
Joined
Aug 5, 2017
Messages
33

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows
You're welcome! Glad to help.
 

DarrenK

New Member
Joined
Aug 5, 2017
Messages
33

ADVERTISEMENT

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

Well-known Member
Joined
Mar 5, 2018
Messages
1,304
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Aug 5, 2017
Messages
33
JKLMNOPQRST
AVG IB WrapAVG IB HandleAVG OB TT*UtilizationWrap Time*Hold Time*Not Ready Time*Approved RC Time*Lunch Time*Break TimeApp Use of OBOutbound
0:01:470:08:170:03:4788.3%0:39:550:26:210:08:151:18:090:25:060:25:38#DIV/0!0:16:04
0:35:000:36:00
VARIABLE%AVG0:12:260:59:530:52:430:33:000:35:000:36:00
ROW50% above50% above100% above400% above13% above13% above
HANDLEWRAPHOLDNOT RDYLUNCHBREAK
 

DarrenK

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

CDEFGHIJ
DateAVG IB Handle%avgWrap 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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,252
Messages
5,600,543
Members
414,387
Latest member
Vincent88

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