Index Match offset?? but I'm clueless!! No idea how to start

[walt1122]

Hi All, having trouble posting code, keep getting an error!, but...
I have 12 tabs labeled 1st qtr. 2003 through 4th qtr. 2005. Each tab has names and the time these people worked, in hours, for us. Different people different hours. I want to develop a summary sheet showing each person down the left hand side and the total number of hours charged against the "Master Trust" So for example, The person Mink Uaamnichai in cell B12 had all 100% of her hours against the master trust in D16. So I need to capture and combine from each tab the percentage of time against the trust for each person. thanks for any help cause I could really use it.
thanks

Walt

2003 - 2005 Time Summary.xls
	B	C	D	E	F	G
7			MASTER	NON-MASTER	HOLIDAY,	TOTAL
8	DEPARTMENT MEMBER		TRUST	TRUST	SICK, ETC.	HOURS
9
10
11
12	J. Burden		552	77	26	655.00
13
14	% (Including Vacation, Holiday, Sick Time)		84.27	11.76	3.97	100.00
15
16	% (Excluding Vacation, Holiday, Sick Time)		87.76	12.24	0.00	100.00
17
18
19	D. Colby		563	13	10	586.00
20
21	% (Including Vacation, Holiday, Sick Time)		96.08	2.22	1.71	100.00
22
23	% (Excluding Vacation, Holiday, Sick Time)		97.74	2.26	0.00	100.00
24
25
26	R.J. Diemer		548	21	56	625.00
27
28	% (Including Vacation, Holiday, Sick Time)		87.68	3.36	8.96	100.00
29
30	% (Excluding Vacation, Holiday, Sick Time)		96.31	3.69	0.00	100.00
31
4th Qtr. 2005

 

[Domenic]

It's not clear to me what it is you'd like summed. First you say you'd like the 'total number of hours charged against the Master Trust' which tells me you want D12, but then you say that you 'need to capture and combine from each tab the percentage of time against the trust for each person' which is D16. Can you clarify? Also, is each sheet in the exact same format?

 

[walt1122]

Sorry, I was having trouble with the HTML?. But in this examle one name in particular is J. Burden. In D16 we see a percentage of hours 87.76% (% (Excluding Vacation, Holiday, Sick Time) worked and I would like to have a sheet show his name along with the other names on this sheet and all the other 11 sheets ( 1st qtr. 2003 - 4th qtr. 2005). All the sheets are set up the same that is one of the reasons I thought Index Match with an offset would work just don't know how to do it. I think I can figure it out with VLOOKUP with some additional work but it would be sloppy at best and just thought someone could offer some assistance with this request.

So the summary sheet would have the quarters across the top and the names down the side with their respecive quarterly totals next to them as in:
J. Burden 87.76%
D. Colby 97.74%
R.J.Deimer 96.31%

and so on...

thanks

Walt

 

[Domenic]

Assumptions:

On your summary sheet...

1) First row, starting at B1, contains the quarter/sheet name, such as 1st qtr. 2003, 2nd qtr. 2003, 3rd qtr. 2003, etc.

2) First column, starting at A2, contains the worker name

Formula:

B2, copied across and down:

=SUMIF(INDIRECT("'"&B$1&"'!B12:B26"),$A2,INDIRECT("'"&B$1&"'!D16:D30"))

Change the ranges accordingly.

Hope this helps!

 

[walt1122]

Hi Domenic, thanks for the help... one more favor, can you or someone explain to me how it works and then maybe I can figure out what is going wrong and why I'm getting the #REF! in I14 with the formula as: SUMIF(INDIRECT("'"&B$12&"'!B12:B26"),$B16,INDIRECT("'"&B$12&"'!D16:D30"))
thanks
Walt

2003 - 2005 Time Summary.xls
	B	C	D	E	F	G	H	I
12	J. Burden		552	77	26	655.00		OK
13
14	% (Including Vacation, Holiday, Sick Time)		84.27	11.76	3.97	100.00		#REF!
15
16	% (Excluding Vacation, Holiday, Sick Time)		87.76	12.24	0.00	100.00
17
4th Qtr. 2005

 

[Domenic]

Make sure that B12, which contains the sheet name, matches the sheet name on the sheet tab exactly. See if there are any extra spaces, or whether you've ommitted the period, etc. Does this help?

 

[walt1122]

Hello again, Domenic, I'm not getting it. I don't understand the B12 reference. B12 is the guys name not the sheet name? So, I do apologize if I sound obtuse but I'm not following from your example.
I need to have the formula look for the persons name down the left hand side and give me the percentage from column D where it is the percentage % (Excluding Vacation, Holiday, Sick Time) in this case it is like this:


J.Burden is in B12 and below him in B16 is the wording
% (Excluding Vacation, Holiday, Sick Time) and from the formula in D16 we get the 87.76%. From the earlier sheet the same would hold true for the next two people.

D. Colby would be 97.74%

And

R.J.Deimer is 96.31%

So the summary sheet would have the quarters across the top and the names down the side with their respecive quarterly totals next to them as in:
J. Burden 87.76%
D. Colby 97.74%
R.J.Deimer 96.31%
for the quarter in question but then would show each of the additional quarters working off to the right.

the reason I think I want this code is to account for people's names and percentages over time who are here one quarter or two and gone the next. This way I could capture the percentages for the time they did work for us. Then maybe have an #N/A or something showing they were not included in the quarter.

thanks

Walt

 

[Domenic]

Can you attach a sample of your summary sheet?

 

[walt1122]

sure can but not much to see. As I mentioned I was trying to do it with VLOOKUP but thought there had to be a better way. Plus I have to figure out how to do the isserror and or ISNA to get rid of the #DIV/0 and #N/A

thanks

Walt

2003 - 2005 Time Summary.xls
	A	B	C	D	E	F	G	H
1				1st Qtr. 2003	2nd Qtr. 2003	3rd Qtr. 2003	4th Qtr. 2003	1st Qtr. 2004
2	Mink Uaamnuichai			100	100	100	100	100
3	J. Burden			90.49079755	90.58295964	91.87145558	91.89189189	91.98717949
4	D. Colby			97.37827715	97.8021978	97.48549323	97.38095238	97.75474957
5	R.J. Diemer			88.19776715	97.28813559	90.75144509	96.99646643	97.30134933
6	Leonard J. Lovito			90.28213166	90.06085193	90.35947712	90	90.20866774
7	D. McGinnis			100	100	100	100	100
8	M. Shapiro			#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
9	R. Shin			94.57364341	97.18574109	96.3028169	94.14990859	94.41340782
10	E. Tokar			96.9924812	96.02076125	96.58703072	90.6870229	96.70164918
11	D. Kitani			#N/A	#N/A	#N/A	#N/A	#N/A
12	H. Bansal			#N/A	#N/A	#N/A	#N/A	37.31343284
13	B. Grippi			#N/A	#N/A	#N/A	#N/A	91.66666667
14	C. J. Malone			#N/A	#N/A	#N/A	#N/A	#N/A
summary sheet

 

[Domenic]

On your summary sheet, enter the following formula in D2, copy down and across...

=SUMIF(INDIRECT("'"&D$1&"'!B12:B26"),$A2,INDIRECT("'"&D$1&"'!D16:D30"))

Make sure that there's a corresponding sheet for each sheet listed in the first row, D1:H1, and that the sheet names listed match the names in the sheet tabs exactly. Otherwise you'll get a #REF! error value.

Does this help?