Cell distance calculating

[ElliotDark]

Hey guys, I really need some help on this. Been trying to work out a offset formula on this but the table size isn't always the same, it can be 1-30 days.

John Doe	in	out	Total
21.april.2012	20:00	22:00	4 hrs
22.april.2012	20:00	22:00	4 hrs
		Total:	8 hrs

<tbody>
</tbody>

Trying to use the name as the key, like this:

Name	Worked hrs
John Doe	8 hrs

<tbody>
</tbody>

Is it possible to find the distance between "John Doe" and the cell with the total and use that as the offset or does anyone have a better idea on how to do this?

I really appricate your time and effort!

 

[wizz]

Hi,
With the function MATCH you can find the row with "Total:"
Example:MATCH("Total:";C1:C65000;0)-1
and then use OFFSET.
Assuming that your example table starts with cell A1, the final formula is:
=OFFSET(D1;MATCH("Total:";C1:C65000;0)-1;0)

 

[ElliotDark]

Hi,
With the function MATCH you can find the row with "Total:"
Example:MATCH("Total:";C1:C65000;0)-1
and then use OFFSET.
Assuming that your example table starts with cell A1, the final formula is:
=OFFSET(D1;MATCH("Total:";C1:C65000;0)-1;0)

Thank you Wizz, this works. Im trying to move it down as I have a multiple time sheets. I only need to move both D1 and C1 to another range like which would just be the next name.

Do you have any idea on how to do that?

Thanks again!