Multiple Lookups & Merged Cells

[bqbowden]

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.

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

<tbody>
</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>

 

[Johnny Thunder]

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.

 

[bqbowden]

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.

 

[Johnny Thunder]

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.

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.