Formula return value in top row when value first & last appears in table

[martiniextradry]

I need to get a formula to read a table, similar to below, and tell me when a person's shift starts and ends. Here is a rough idea of it:

A B C D E F G H I
1 Name 4:00 4:30 5:00 5:30 6:00 6:30 7:00 7:30
2 Martin O A F F F F W
3 Kevin A F

That is a small section of it. The O means opening, the A is shift preparation, F means floor manager and W is one of many letters that say other jobs (this letter means very little). What I need it to do at first is just look for the first cell (and then one for the last too) with a value in it and put the value in row 1. So for the small example above it would need to produce:
A B C
1 Name Start Finish
2 Martin 4:00 7:30
3 Kevin 6:30 7:30

with B2 and B3 having the formula that works out the first cell with a value and C2 and C3 having the same formula but for the last cell with a value.

I tried with HLOOKUP but think I might be barking up the wrong tree.

PS It says 7:30 because they have a value in 7:00, so they will leave at 7:30 (the first blank cell)

PPS it doesn't need to understand what letter is what, I may add that at a later date.

HmmmPS it seems to have removed my spaces so you can't see how my cells line up

 

[T. Valko]

I need to get a formula to read a table, similar to below, and tell me when a person's shift starts and ends. Here is a rough idea of it:

A B C D E F G H I
1 Name 4:00 4:30 5:00 5:30 6:00 6:30 7:00 7:30
2 Martin O A F F F F W
3 Kevin A F

That is a small section of it. The O means opening, the A is shift preparation, F means floor manager and W is one of many letters that say other jobs (this letter means very little). What I need it to do at first is just look for the first cell (and then one for the last too) with a value in it and put the value in row 1. So for the small example above it would need to produce:
A B C
1 Name Start Finish
2 Martin 4:00 7:30
3 Kevin 6:30 7:30

with B2 and B3 having the formula that works out the first cell with a value and C2 and C3 having the same formula but for the last cell with a value.

I tried with HLOOKUP but think I might be barking up the wrong tree.

PS It says 7:30 because they have a value in 7:00, so they will leave at 7:30 (the first blank cell)

PPS it doesn't need to understand what letter is what, I may add that at a later date.

HmmmPS it seems to have removed my spaces so you can't see how my cells line up

That highlighted section makes it sound like you want to find the next empty cell after the last cell with an entry?

See if this is what you had in mind.

Book1
	A	B	C	D	E	F	G	H	I
2	Name	4:00	4:30	5:00	5:30	6:00	6:30	7:00	7:30
3	Martin	O	A	F	F	F	F	W	_
4	Kevin	_	_	_	_	_	A	F	_
Sheet1

Book1
	A	B	C
10	Martin	4:00	7:30
11	Kevin	6:30	7:30
Sheet1

This formula entered in B10 and copied down:

=INDEX(B$2:I$2,MATCH("*",INDEX(B$3:I$4,MATCH(A10,A$3:A$4,0),0),0))

Format as Time

This formula entered in C10 and copied down:

=INDEX(B$2:I$2,MATCH("zzzzz",INDEX(B$3:I$4,MATCH(A10,A$3:A$4,0),0))+1)

Format as Time

Assumes there will always be an empty cell at the end of the range.

 

[martiniextradry]

Thats wonderful thank you Bill!

It's not quite working right on the end of shift ones though. It is putting the result of the first person (Martin) for everyone. What do the z's mean?

 

[martiniextradry]

D'uh! Ignore me, I just forgot to put the $s in when I made it the actual box size.

Thanks so much Bill. Much appreciated

 

[T. Valko]

Thats wonderful thank you Bill!

It's not quite working right on the end of shift ones though. It is putting the result of the first person (Martin) for everyone. What do the z's mean?

Assuming I understood what you wanted to do...

The formulas work for me.

Here's a small sample file that demonstrates this.

ZZZmartiniextradry.xls 15kb

http://cjoint.com/?AGzfvuxTApY

The "zzzzz" is how we find the last (right-most) text entry on a row.