1. ## Lottery Workbook Update

I've improved my workbook and rethought my needs from the previous thread at Capturing a count that later changes, and am posting the new situation here. Thanks to all who have worked on this matter so far.

This applies to an updated Excel workbook that can be found HERE. Just click the Ben Franklin picture to download Lottery32TEST.xls, then look at the wk3 sheet.

I use this workbook to manage a large lottery pool. Here's how:

Drawings for the pool are held four times a week. MegaBall drawings are on Tuesday and Friday; PowerBall drawings are on Wednesday and Saturday. Each wkn worksheet records the results for one week.

Soon after each drawing, lottery officials post the winning numbers on their website. I then enter them manually into the current wkn worksheet, which calculates our winnings and provides me some text to post onto Twitter. Excel automatically posts the numbers and the winnings to the Summary sheet. From there I post them to our lottery website HERE. The Summary and wkn sheets remain as permanent records of the results of this drawing.

If the drawing took place on a Friday, I click the "Make New Sheet" command button (at the far left, right below the alligator logo), which creates a new sheet for wkn+1. The new sheet is based on the wk0 sheet, updated through processes developed with great help from Loungers.

Several hours after each drawing, lottery officials post the "Est. Annuity" and "Est. Cash" values for the next drawing of its kind (MegaMillions or PowerBall). Sometime after this, often the next morning, I manually enter these values into the appropriate wkn worksheet. As when I post the winning numbers, this act updates the Summary worksheet and gives me some text to post to Twitter. I then use the updated Summary to update our lottery website.

Here's what I'm trying to accomplish:

Each week's worksheet handles four drawings, and has many named cells. The cell names are fairly intuitive, and are based on the two MegaBall drawings (generally named MA... and MB...) and two PowerBall drawings (PA... and PB...)

We sell shares in the Lottery Pool from about a month before it starts, right up until its last day. Those sales are recorded in the CountOfShares cell on the Participants sheet. This value is constantly changing due to ongoing sales of shares. Its value as of the date of a specific drawing is important, since it is used in the "winnings per share" calculation on the wkn and Summary sheets, which become our permanent records.

Right now, I manually enter the value of the current CountOfShares into the MAShares, MBShares, PAShares and PBShares cells, as soon as I know the updated Annuity and Cash values for each drawing. I change them manually if CountOfShares changes between the first posting of the values and the time of the drawing.

(This is different, and better, than the way I handled them in the past. It also requires me to stay alert for changes in CountOfShares between the time a jackpot is first posted and the time of the ultimate drawing.)

I'd like to use Excel to automate some of this. Specifically:

1. I'd like to have Excel put the current value of CountOfShares into MAShares or the MB, PA, and PB equivalents, as soon as a number greater than 7,000,000 is manually entered in MAAnnuityValue or the MB, PA, and PB equivalents.
2. Further, I'd like Excel to update that value as soon as a number is posted to MAN6, or the MB, PA, and PB equivalents (this is the final number drawn in each of the drawings).
3. I'd like to have the option of changing that value manually, if we sell some shares between the initial entry of the Annuity Value and the time of the drawing.
4. If step #2 is hairy, difficult, or impossible, I can do without it and rely on #3 and my own alertness for the interim and final updates.

2. I've looked around various Excel help sites. Apparently one can write VBA code that executes when a cell is changed. I think I need such code to plug the current CountOfShares into MAShares (and the MB, PA, and PB versions) when MAAnnuityValue is changed, and to do the same thing when MAN6 is changed. Coming up with that code is still beyond me, though.

Also it was suggested that I put in a command button to plug the current CountOfShares into MAShares when it is clicked. I can create a macro to do that, but I've got a mental block on what the macro code should be.

Hints, anyone?

3. ## Stupidity Resolved

Sleeping on the problem gave the obvious answer. I've recorded macros to update the share counts on MA, MB, PA, and PB, and put each one on its own command button.

Now, how to run the macros when the previously mentioned cells are updated? (I'm working on it.)

4. The "command button solution" is working so well that I'm abandoning further efforts to improve the system.

Once again, simplest is (usually) best.

Once again, taking the time to document a problem completely often leads directly to the solution of the problem.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•