Adding a formula to accomodate for a rollover number in a Lotto `bonus ball` spreadsheet

Hi all, hope you can help on this minor problem.

Background (don't have to read this)

I work in a warehouse that now leans towards the admin side. Though i had no expeience with excel, over the last 6 months i've been asked to make various spreadhseets for work, ([sarcasm]Because i repair computers, it must mean i know how to use all the computer software in existence[/sarcasm]) to which i've done so to the best of my ability.

I've started to play about with excel to brush up on some of its more technical functions. (IF functions & VLOOKUP really) as i've personally started to find it very interesting. However in one of my `projects` i've reached a snag. As i've come so close to completing it i really do want to get it done, but have no idea how to cater for the problem.

The Spreadhseets Aim (Attached)

This spreadhseet itself is simply recording a `bonus ball` punt we do every week, everyone has a small stake on one or more numbers (Sometimes half/half on single numbers, but i've accounted for this) & i wanted to record the frequancy of wins for each person, as well as the overall profit & loss in fine detail (As some stake more than others) i also wanted to make it in to a kind of `league table` sort of thing with the person yielding the greatest profit at the top. All of this has been achieved.

The problem

the problem is that there is an untaken number that will rollover the winnings to the following number. I don't know how to account for this, but i'd like a solution that will add this automatically to the winner of the rollover profit, as well as his/her own win for the week. Meaning that all i have to continue doing is manually inputting the bonus ball number & the rest is completed automatically.

The Spreadhseet in detail

Sorry if the SS is a bit all over the place, perhaps there was a simpler way to achieve this, but i just kind of bundled up everything i knew about excel & hoped for the best.

Page 1 "Frequency" - This shows all entrants & uses the countif function on the "Results" spreadsheet to tally up the frequency of each number, there is also a hidden list of the entrants in Collumn H & I so that i could create a VLOOKUP database for the "Results" page of the SS.

Page 2 "Results" - Shows the results of the bonus balls from when the competition started, uses a simple date + 7 rule & feeds off the VLOOKUP database on page 1 so that i only have to put in the number & the rest autocompletes.

Page 3 "Profit & Loss" - This ones uses a few more functions, Collumn C multiplies every completed draw on the Results page, by the amount the person stakes each week. (using countif>0) Collumn D takes the results for each persons number(s) in the "Frequency page" & multiplies it by either 48 or 24, depending on whether they have a shared stake or not, then adds these together if they have more than one number to then display each persons total winnings. Finally, Collumn E simply takes C away from D to give the persons total profit. I then set it all as list so that i could simply sort profit/loss by descending to display who's made the greatest profit.

---

Sorry if this problem seems petty or not worth any time, but as i said i've reached the end of this, & i don't mind saying that creating this has pushed me to the limits of my knowledge on excel, which is pretty poor as it is.

Any help is appreciated, & i'll gladly return the favour if anyone needs to know the correct way to lift a box, they spare no expense on us warehouse lads, a full one hour video we have on the subject.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

I see you have The problem section so try something like:

Skip untaken number to the following number for lottery sistem