Automatically copy text data from one cell, into another cell, by entering the row number

Hi guys.
I've got a sheet drawn up for our Work Lotto Bonus ball game
Its simple enough layout, with the Rows representing the 49 numbers in the lotto, and the columns representing the Week/draw dates.
I then enter the names of people taking part, in their chosen week/number cell.
The first 4 rows, before the numbering starts, i have Date, Winning number, winners name, and the winning prize amount.
If there is no winner, the pot rolls over into the following week.
I can manually enter all this info, but what i want to do is type in the winning number, and then have it automatically fill in the name of the winner.

For example:
I type in number 30 in row 2
In row 30, is Bob (lucky Chap)
The sheet automatically takes bobs name, and copies it into row 3, under the winning number.
There is a winner, The prize amount (in row 4) is won, so the following weeks pot stays the same.

If there is no winner, no name appears in row 3, and the prize pot in row 4, then gets added to the pot already in Row 4 of the next column.

I'm very new to excel and have next to no experience in it so any help would be hugely appreciated.
I have attached a demo Xls of the spreadsheet i am trying to complete.
Thanks in advance

Originally Posted by acathan

Did that really just take you 7 minutes??

EDIT: The only 'problem' is when there is no number in Row 7. Ie: before the draw has taken place. Instead of leaving the winners box blank, it shows #VALUE!
I also modified the formula in row 10 to '=IF(B8="",B10+COUNTA(C11:C59),COUNTA(C11:C59))' which adds the previous weeks pot, to the current week, allowing the total to change as players are added, however it has the same problem as above. If no number in Row 7, it shows #VALUE!
Functionally, its no problem, but i print it out each week, so it looks a bit funny.

For the first problem try this similar version in B8:F8:

Formula:

=IFERROR(INDEX(B$11:B$59&"",MATCH(B$7,$A$11:$A$59,0)),"")

For the second problem after doing the above see if your modified "pot" formula still produces an error. It didn't when I used it in conjunction with formula I posted here. I suspect the #VALUE error you were getting was derived from the #VALUE error in the first formula (B8:F8).

Hope this helps.

Thanks guys.
A combination of your answers corrected it perfectly.
I used GC Excels Formula, But after trying it in B7 and not working, i presumed B8 was meant, as suggested by Flameretired.
FYI, I did tried both formulas and they both appear to perform the same, working as it should.

However...

It has thrown up another 'error'. I can live with this one if its more hassle than its worth, but i like perfection and you guys know your stuff
The weeks that have no Number entered yet, the next weeks pot is showing it as a rollover. But obviously, we dont know if it will be yet. It would be nicer if the pot stayed as its individual weekly pot, until the information was entered confirming it as a rollover.
Currently, The formula in B10 is '=COUNTA(B11:B59)' and C10:F10 is '=IF(B8="",B10+COUNTA(C11:C59),COUNTA(C11:C59))'
Like is said, i can live with it, as you've been very helpful already.
Thanks