Solved: Creating a dynamic concatenation from a list

[helix123]

I have a spreadsheet which has list of companies with each company given a ranking.

Also I have a clock timer which shows what minute it is.

My aim is to try and display the top ranking company names separated by a comma in a cell based on what the current minute (cell E6) is up to i.e. On the 3rd minute display the text "AOL, Apple, Air Pacific" in a cell as these three companies are ranked 1st, 2nd and 3rd.

On the fourth minute in the cell it would then display the text "AOL, Apple, Air Pacific, BMW" as BMW ranks 4th on the list.

See Attached Spreadsheet.

Is there a way to do this without having to use a long if statement?

Cheers

[Bob Phillips]

As before, np formula use the timer

Sub SetTimer()
Dim clock As Date
Dim i As Long
If datStartTime = 0 Then datStartTime = Now
clock = Now - datStartTime
If Second(clock) = 0 Then
If Minute(clock) = 0 Then
Range("D18").Value = Range("B2")
Else
Range("D18").Value = Range("D18").Value & "," & Cells(Minute(clock) + 2, "B").Value
End If
End If
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "RecalcTimer"
End Sub

[helix123]

Excellent!