Automatic Absolute Cell Reference Formula

[QU4487]

Please excuse the sanitised description. I have a list of people conducting a number of events each, over time. I need to be able to tell the time difference between the last event for each person and the previous events they conducted. I have attached an example of how I can do it manually, using absolute cell referencing, but the list is in the hundreds and it's a bit tedious doing it this way.

Any help would be greatly appreciated.


[TABLE="width: 700"]
<tbody>[TR]
[TD]Individual[/TD]
[TD]Event Date Time[/TD]
[TD]Difference From Final Event[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]07/06/2013 14:50:07[/TD]
[TD]00:00:00
[/TD]
[TD]=B$2-B2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]07/06/2013 14:27:47[/TD]
[TD]00:01:48[/TD]
[TD]=B$4-B3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]07/06/2013 14:29:35[/TD]
[TD]00:00:00[/TD]
[TD]=B$4-B4[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]07/06/2013 14:28:11[/TD]
[TD]00:14:35[/TD]
[TD]=B$7-B5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]07/06/2013 14:41:55[/TD]
[TD]00:00:51[/TD]
[TD]=B$7-B6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]07/06/2013 14:42:46[/TD]
[TD]00:00:00[/TD]
[TD]=B$7-B7[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]07/06/2013 13:50:52[/TD]
[TD]01:01:18[/TD]
[TD]=B$11-B8[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]07/06/2013 13:50:53[/TD]
[TD]01:01:17[/TD]
[TD]=B$11-B9[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]07/06/2013 14:13:24[/TD]
[TD]00:38:46[/TD]
[TD]=B$11-B10[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]07/06/2013 14:52:10[/TD]
[TD]00:00:00[/TD]
[TD]=B$11-B11[/TD]
[/TR]
</tbody>[/TABLE]

 

[WinteE]

As the last time is always on top, you can use =VLOOKUP() to find the previous time.

For example if your last entered individual B is on row 3, column A then the formula should be =IF(ISNA(VLOOKUP("B",A4:B1000,2,FALSE)),0,VLOOKUP("B",A4:B1000,2,FALSE))

Success,
Erik

 

[WinteE]

Double deleted

 

[FormR]

One possible way:

Excel Workbook
	A	B	C	D
1	Individual	Event Date Time	Difference From Final Event	Formula
2	A	07/06/2013 14:50	00:00:00	0:00:00
3	B	07/06/2013 14:27	00:01:48	0:01:48
4	B	07/06/2013 14:29	00:00:00	0:00:00
5	C	07/06/2013 14:28	00:14:35	0:14:35
6	C	07/06/2013 14:41	00:00:51	0:00:51
7	C	07/06/2013 14:42	00:00:00	0:00:00
8	D	07/06/2013 13:50	01:01:18	1:01:18
9	D	07/06/2013 13:50	01:01:17	1:01:17
10	D	07/06/2013 14:13	00:38:46	0:38:46
11	D	07/06/2013 14:52	00:00:00	0:00:00
Sheet1

 

[davesweep]

Hi,

Try this in D2 and copied down

=INDEX($B$2:$B$11,MATCH(A2,$A$2:$A$11,1))-B2

 

[QU4487]

Eric,

Thanks for the reply, it would do what I'm looking for. However, I think FormR and davesweep have come up with simpler solution. And I always prefer simple.

Guys, I'm in awe.

Many thanks

 

[pgc01]

Hi

Another option:

=LOOKUP(A2,$A$2:$A$11,$B$2:$B$11)-B2