Offset Function Problems

[theglassviolin]

I'm trying to calculate time for our department when people pick up shifts. I was trying to have it automatically pull the hours they worked by having the formula to find their name (like in the count option) offset it three cells to the right for that value, and then add all of those individual shifts of that same staff together. The problem is that offset seems to only be able to reference one cell and can't have it locate specific values. Since staff is picking up different shifts, I'm plugging them all over the place but would like to have it automatically calculate their time when plugged into a shift.

Any and all help would be greatly appreciated.

 

[KRice]

Could you provide a small example showing what your worksheet looks like, specifically where the names are listed, where the name to be found is located, and where the shifts/hours are located? If possible, use the XL2BB add-in to post the example.

 

[theglassviolin]

Could you provide a small example showing what your worksheet looks like, specifically where the names are listed, where the name to be found is located, and where the shifts/hours are located? If possible, use the XL2BB add-in to post the example.

Oh man, I don't know what XL2BB add-in even is, I'm sorry.

Attached is a screen shot. I want the system to be able to pull the hours associated with the name that is put into that shift. So if I put "Piper" in a shift, the time calculates fine, but I would like to be able to pull the hours directly into another cell that adds up all of the hours specific to that one employee. I'm not even sure if that's something that can be automated with the way I have it laid out.

I tried to make it offset three columns from the name (to grab the hours) and then add all of the hours together, that way any time I plugged in someone's name it would know to grab the total hours (which is three columns away), but unfortunately offset only works with specific cells. Below all of this I have all of my employees listed and have been manually adding their hours together, which works, but clearly offers itself to human error.

Thank you for responding.

 

[KRice]

A link in my signature block will take you to a page that describes the XL2BB add-in, offers installation help, and provides a link for downloading the add-in. Assistance and resolution of problems are greatly facilitated when the add-in is used to exchange worksheet structure, formulas, etc. I still do not understand where you want the results to appear. Below is an idea that creates a list of all names appearing in column A and then the total hours for each name are determined with a formula. In this example, the time duration in column D is expressed in hours:minutes, but Excel internally treats this as a number expressed as some fraction of a day (so 12 hours...12:00...is 0.5 days). So when the total hours are summed, Excel is expressing the total in terms of days; therefore the results are multiplied by 24 to yield hours.

Book5
	A	B	C	D	E	F	G	H	I
1		Sunday, June 18th AM				Coverage Analysis		Staff	Total Hours
2	Name:	Start	Stop	Hours	Role	AZ		Name1 Piper (AZ, IN, TN, AR, NC	28.00
3	Name1 Piper (AZ, IN, TN, AR, NC	6:00	18:00	12:00	TL	IN		Name2 (TN, AK)	12.00
4	Name2 (TN, AK)	7:00	19:00	12:00		TN		Name3	12.00
5	Name3	7:00	19:00	12:00	BHU	AR		Name4 (IN, TN, AR, NC, NM)	21.25
6	Name4 (IN, TN, AR, NC, NM)	7:00	19:00	12:00		NC		Name5 (AZ, TN, NM)	24.00
7	Name5 (AZ, TN, NM)	7:00	19:00	12:00		NM		Name6 (AZ, IN, TN, AR, NM	24.00
8	Name6 (AZ, IN, TN, AR, NM	7:00	19:00	12:00		AL		Name7 (AZ, IN, TN, NC, AL, AK)	24.00
9	Name7 (AZ, IN, TN, NC, AL, AK)	7:00	19:00	12:00		AK		Name8 (TN, AK)	12.00
10								Name9	12.50
11		Monday, June 19th PM				Coverage Analysis
12	Name:	Start	Stop	Hours	Role	AZ
13	Name1 Piper (AZ, IN, TN, AR, NC	6:00	22:00	16:00	TL	IN
14	Name8 (TN, AK)	7:00	19:00	12:00		TN
15	Name9	7:00	19:30	12:30	BHU	AR
16	Name4 (IN, TN, AR, NC, NM)	10:00	19:15	9:15		NC
17	Name5 (AZ, TN, NM)	7:00	19:00	12:00		NM
18	Name6 (AZ, IN, TN, AR, NM	7:00	19:00	12:00		AL
19	Name7 (AZ, IN, TN, NC, AL, AK)	7:00	19:00	12:00		AK
Sheet4

Cell Formulas
Range	Formula
H2:H10	H2	=SORT(UNIQUE(FILTER(A:A,(A:A<>"")*(A:A<>"Name:"))))
D3:D9,D13:D19	D3	=C3-B3
I2:I10	I2	=24*SUMIF(A:A,H2,D:D)
Dynamic array formulas.

 

[theglassviolin]

Thank you for this. I apparently don't have the option to have a SORT command.

 

[KRice]

That's odd...are you using Excel 365? The SORT function is not essential, but it would make the list of staff easier to use.