Multiple Lookups & Merged Cells

B

bqbowden

New Member
Joined
Nov 15, 2013
Messages
5
Not sure where to even start on this one - hopefully someone can help direct me.

I need to look up a person (i.e. Agent: 63517 Acker, James) then from there, find the section called "Total for xxxx" (i.e. Total for 63517 Acker, James), the find "Available and pull/get the Percent in Adherence. I have a long list of agents I need to do this for. Plus, some cells are merged and/or hidden.


[TABLE="width: 722"]
<tbody>[TR]
[TD="colspan: 21"]Agent: 63517 Acker, James[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 23"]Weekly Summary: 3/1/18 - 3/7/18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Scheduled Activities[/TD]
[TD="colspan: 2"]Scheduled Time[/TD]
[TD="colspan: 2"]Actual Time[/TD]
[TD="colspan: 2"]Min. in Adherence[/TD]
[TD="colspan: 2"]Min. out Adherence[/TD]
[TD]Percent in Adherence[/TD]
[TD="colspan: 3"]+/- Min. in Conformance[/TD]
[TD="colspan: 3"]Percent in Conformance[/TD]
[TD]Percent of Total Schedule[/TD]
[TD="colspan: 4"]Percent of Total Actual[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]ACW[/TD]
[TD="colspan: 2"]00:00[/TD]
[TD="colspan: 2"]01:18[/TD]
[TD="colspan: 2"]0[/TD]
[TD="colspan: 2"]0[/TD]
[TD]0.00%[/TD]
[TD="colspan: 3"]78[/TD]
[TD="colspan: 3"]0.00%[/TD]
[TD]0.00%[/TD]
[TD="colspan: 4"]2.85%[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: 0[/TD]
[TD="colspan: 2"]00:00[/TD]
[TD="colspan: 2"]00:12[/TD]
[TD="colspan: 2"]0[/TD]
[TD="colspan: 2"]0[/TD]
[TD]0.00%[/TD]
[TD="colspan: 3"]12[/TD]
[TD="colspan: 3"]0.00%[/TD]
[TD]0.00%[/TD]
[TD="colspan: 4"]0.44%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: Break[/TD]
[TD="colspan: 2"]02:30[/TD]
[TD="colspan: 2"]02:12[/TD]
[TD="colspan: 2"]125[/TD]
[TD="colspan: 2"]25[/TD]
[TD]83.33%[/TD]
[TD="colspan: 3"]-18[/TD]
[TD="colspan: 3"]88.00%[/TD]
[TD]5.56%[/TD]
[TD="colspan: 4"]4.82%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: Online[/TD]
[TD="colspan: 2"]00:00[/TD]
[TD="colspan: 2"]01:55[/TD]
[TD="colspan: 2"]0[/TD]
[TD="colspan: 2"]0[/TD]
[TD]0.00%[/TD]
[TD="colspan: 3"]115[/TD]
[TD="colspan: 3"]0.00%[/TD]
[TD]0.00%[/TD]
[TD="colspan: 4"]4.20%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: Project[/TD]
[TD="colspan: 2"]01:15[/TD]
[TD="colspan: 2"]00:30[/TD]
[TD="colspan: 2"]30[/TD]
[TD="colspan: 2"]45[/TD]
[TD]40.00%[/TD]
[TD="colspan: 3"]-45[/TD]
[TD="colspan: 3"]40.00%[/TD]
[TD]2.78%[/TD]
[TD="colspan: 4"]1.10%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Available[/TD]
[TD="colspan: 2"]35:45[/TD]
[TD="colspan: 2"]34:04[/TD]
[TD="colspan: 2"]1,959[/TD]
[TD="colspan: 2"]186[/TD]
[TD]91.33%[/TD]
[TD="colspan: 3"]-101[/TD]
[TD="colspan: 3"]95.29%[/TD]
[TD]79.44%[/TD]
[TD="colspan: 4"]74.65%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Closed[/TD]
[TD="colspan: 2"]00:30[/TD]
[TD="colspan: 2"]00:31[/TD]
[TD="colspan: 2"]30[/TD]
[TD="colspan: 2"]0[/TD]
[TD]100.00%[/TD]
[TD="colspan: 3"]1[/TD]
[TD="colspan: 3"]103.33%[/TD]
[TD]1.11%[/TD]
[TD="colspan: 4"]1.13%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Unstaff[/TD]
[TD="colspan: 2"]05:00[/TD]
[TD="colspan: 2"]04:56[/TD]
[TD="colspan: 2"]278[/TD]
[TD="colspan: 2"]60[/TD]
[TD]92.67%[/TD]
[TD="colspan: 3"]-4[/TD]
[TD="colspan: 3"]98.67%[/TD]
[TD]11.11%[/TD]
[TD="colspan: 4"]10.81%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Total[/TD]
[TD="colspan: 2"]45:00[/TD]
[TD="colspan: 2"]45:38[/TD]
[TD="colspan: 2"]2,422[/TD]
[TD="colspan: 2"]316[/TD]
[TD]89.70%[/TD]
[TD="colspan: 3"]38[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 23"]Total for 63517 Acker, James[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Scheduled Activities[/TD]
[TD="colspan: 2"]Scheduled Time[/TD]
[TD="colspan: 2"]Actual Time[/TD]
[TD="colspan: 2"]Min. in Adherence[/TD]
[TD="colspan: 2"]Min. out Adherence[/TD]
[TD]Percent in Adherence[/TD]
[TD="colspan: 3"]+/- Min. in Conformance[/TD]
[TD="colspan: 3"]Percent in Conformance[/TD]
[TD]Percent of Total Schedule[/TD]
[TD="colspan: 4"]Percent of Total Actual[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]ACW[/TD]
[TD="colspan: 2"]00:00[/TD]
[TD="colspan: 2"]02:27[/TD]
[TD="colspan: 2"]0[/TD]
[TD="colspan: 2"]0[/TD]
[TD]0.00%[/TD]
[TD="colspan: 3"]147[/TD]
[TD="colspan: 3"]0.00%[/TD]
[TD]0.00%[/TD]
[TD="colspan: 4"]2.08%[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: 0[/TD]
[TD="colspan: 2"]00:00[/TD]
[TD="colspan: 2"]00:19[/TD]
[TD="colspan: 2"]0[/TD]
[TD="colspan: 2"]0[/TD]
[TD]0.00%[/TD]
[TD="colspan: 3"]19[/TD]
[TD="colspan: 3"]0.00%[/TD]
[TD]0.00%[/TD]
[TD="colspan: 4"]0.27%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: Break[/TD]
[TD="colspan: 2"]05:45[/TD]
[TD="colspan: 2"]05:10[/TD]
[TD="colspan: 2"]296[/TD]
[TD="colspan: 2"]49[/TD]
[TD]85.80%[/TD]
[TD="colspan: 3"]-35[/TD]
[TD="colspan: 3"]89.86%[/TD]
[TD]4.92%[/TD]
[TD="colspan: 4"]4.38%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: Online[/TD]
[TD="colspan: 2"]00:00[/TD]
[TD="colspan: 2"]02:55[/TD]
[TD="colspan: 2"]0[/TD]
[TD="colspan: 2"]0[/TD]
[TD]0.00%[/TD]
[TD="colspan: 3"]175[/TD]
[TD="colspan: 3"]0.00%[/TD]
[TD]0.00%[/TD]
[TD="colspan: 4"]2.47%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: Outbound[/TD]
[TD="colspan: 2"]00:00[/TD]
[TD="colspan: 2"]00:06[/TD]
[TD="colspan: 2"]0[/TD]
[TD="colspan: 2"]0[/TD]
[TD]0.00%[/TD]
[TD="colspan: 3"]6[/TD]
[TD="colspan: 3"]0.00%[/TD]
[TD]0.00%[/TD]
[TD="colspan: 4"]0.09%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: Project[/TD]
[TD="colspan: 2"]03:00[/TD]
[TD="colspan: 2"]02:24[/TD]
[TD="colspan: 2"]129[/TD]
[TD="colspan: 2"]51[/TD]
[TD]71.67%[/TD]
[TD="colspan: 3"]-36[/TD]
[TD="colspan: 3"]80.00%[/TD]
[TD]2.56%[/TD]
[TD="colspan: 4"]2.04%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]AUX: Training[/TD]
[TD="colspan: 2"]01:00[/TD]
[TD="colspan: 2"]01:00[/TD]
[TD="colspan: 2"]60[/TD]
[TD="colspan: 2"]0[/TD]
[TD]100.00%[/TD]
[TD="colspan: 3"]0[/TD]
[TD="colspan: 3"]100.00%[/TD]
[TD]0.86%[/TD]
[TD="colspan: 4"]0.85%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Available[/TD]
[TD="colspan: 2"]81:15[/TD]
[TD="colspan: 2"]77:24[/TD]
[TD="colspan: 2"]4,490[/TD]
[TD="colspan: 2"]385[/TD]
[TD]92.10%[/TD]
[TD="colspan: 3"]-231[/TD]
[TD="colspan: 3"]95.26%[/TD]
[TD]69.44%[/TD]
[TD="colspan: 4"]65.66%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Closed[/TD]
[TD="colspan: 2"]08:00[/TD]
[TD="colspan: 2"]00:54[/TD]
[TD="colspan: 2"]53[/TD]
[TD="colspan: 2"]427[/TD]
[TD]11.04%[/TD]
[TD="colspan: 3"]-426[/TD]
[TD="colspan: 3"]11.25%[/TD]
[TD]6.84%[/TD]
[TD="colspan: 4"]0.76%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Unstaff[/TD]
[TD="colspan: 2"]18:00[/TD]
[TD="colspan: 2"]25:14[/TD]
[TD="colspan: 2"]1,020[/TD]
[TD="colspan: 2"]113[/TD]
[TD]94.44%[/TD]
[TD="colspan: 3"]434[/TD]
[TD="colspan: 3"]140.19%[/TD]
[TD]15.39%[/TD]
[TD="colspan: 4"]21.41%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Total[/TD]
[TD="colspan: 2"]117:00[/TD]
[TD="colspan: 2"]117:53[/TD]
[TD="colspan: 2"]6,048[/TD]
[TD="colspan: 2"]1,025[/TD]
[TD]86.15%[/TD]
[TD="colspan: 3"]53[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col><col><col><col><col><col span="4"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>[/TABLE]
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Why not just add a new hidden column in column "A" and put a a formula that will return everything in column B (formely A) with this, you should be able to get all the row headers like "Available" and if your merged cells that contains "Total for James Acker you can also get this returned into a regular cell, then you just have to do a sumproduct to get the Adherence column. If you link a spreadsheets I can look at it for you and try it if this doesn't make sense.
 
Upvote 0
I cannot link the spreadsheet. Plus, I have to dump the data everyday to create the report - trying not to have to do any special things like you suggest. I can have a reference page, which I was planning on doing then dump the report into a "data" worksheet. Then the formulas would work off of that.
 
Upvote 0
That works too, and you could write up a quick macro that will update your formulas to the newly created report in the event the last row is constantly moving.

bqbowden said:
I cannot link the spreadsheet. Plus, I have to dump the data everyday to create the report - trying not to have to do any special things like you suggest. I can have a reference page, which I was planning on doing then dump the report into a "data" worksheet. Then the formulas would work off of that.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
600
awoohaw
awoohaw
K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
514
XLfriend777
XLfriend777
A
  • Solved
Replace formula with zero when populate Right mark for two sheets
Replies
3
Views
367
awoohaw
awoohaw
HappyChappy
  • Solved
combining multiple cell Calculations
Replies
3
Views
306
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

Share this page

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
Back
Top