data extraction from a dynamic list

[gsdanger]

Good Day all,
I need assistance with extracting a number and time out of a dynamic list.
I have a list of competitors in column B. This list (column) is dynamic. That is, it varies with the number of competitors (could be 2, or could be 22. In this example there are only 5 runners). The end of the list is always indicated by the words "End of List" in column B. 3 rows down from this, in column C is the Heat number and the Heat time. In my example, these are at C10 and C11. Naturally, as the list varies in size, so does the actual cell references of the heat number and the time. I want to extract the heat number and the heat time from this dynamic list and place them in cell reference H1 and H2 respectively.

Here is a small example of what i am talking about.

a b c d e f g h i
1 runner
2 1
3 2
4 3
5 4
6 5
7 End of List
8
9
10 800m heat 3
11 Heat time 11:15 AM

Because this list can be dynamic, the cell locations of the heat number and the heat time changes, making it very hard to place the heat number and the time in the H column.

Thought the "End of List" could be used as the static anchor for using something like a Do Until. value = "End of List", then use the active cell offset to pickup the heat number and times.??? I don't know how to do this, so I need assistance. If you can assist, please give me an example to follow.

Regards. gsdanger.

 

[Peter_SSs]

I think you should be able to do this with standatd formulas. Is this what you want?

Excel Workbook
	B	C	D	E	F	G	H
1	runner						Heat 3
2	1						11:15 AM
3	2
4	3
5	4
6	5
7	End of List
8
9
10	800m	Heat 3
11	Heat Time	11:15 AM
gsdanger

 

[gsdanger]

Thanks Peter_SS for your prompt reply.

Great that I may be able to achieve what I'm trying to do without delving into the VBA area.
I understand the H1 formula, but could you explain a bit, the H2 formula?

I don't understand it.

As I mentioned in my post, I am fairly new to this programming and formula stuff.

 

[Peter_SSs]

9.99E+307 is a VERY large number (999 followed by 305 zeros). If the number being looked for is larger than any of the numbers in the lookup range the LOOKUP function returns the last number in the lookup range.

Since Time in Excel is stored as a number, then providing there are no other numbers below the time in C1:C30 (or any larger range you want to use) the LOOKUP function will return the Time.

If your time in column C is always just a time within a day, we could just as well have used
=LOOKUP(1,C1:C30)

 

[gsdanger]

Thanks you Peter_ss for the explanation.
All works Ok now.
Once again, thank you for your assistance. No doubt, I'll be back!.

Have a good Day....Kind Regards.

gsdanger