Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Capturing a count that later changes

    This is, at least for now, the last unsolved puzzle in my lottery workbook.

    My lottery pool sells shares on an ongoing basis. My workbook keeps track of the number of shares sold in a cell named CountOfShares. As we sell more shares, that count increases.

    When a lottery drawing is made (four times a week), I want to capture that day's CountOfShares for use in several calculations, the results of which appear on various sheets within my workbook. I don't want the existing calculations to change as CountOfShares increases in the future.

    If I were doing this manually, I'd copy CountOfShares at the time of a drawing, and use Paste>Special>Values to plug it into the four or five places where it is used for calculations.

    I'm looking for a way to automate that task, but I hardly know where to begin. I don't know how to capture today's value so it doesn't change in the future, and I don't know how to tell Excel to capture it.

    When a drawing is made, I manually input the drawn numbers on the worksheet that applies to this week's drawings (their pre-updating values are all null). As I think about it, those might be the events that trigger the automated process. Another could be the match of TODAY() and one of the predetermined drawing dates that appear on the various worksheets.

    I lay my need before the Gurus.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Lou,

    Capturing the value of CountOfShares is easy with vba. The event that triggers it is the challenge. What you want to do with the "stored" value will follow. So, before you lay your need, can you post a sample of the sheet?

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I'd like to make the whole workbook available, but it's over 5MB and contains a lot of personal information about players. I'm tempted to mask the personal information and put the whole thing up on Dropbox or somewhere, but I don't have any experience with that. Also, the workbook changes every few days due to normal activity and due to my constant meddling with upgrades.

    In the meantime, here's a sample of the page where I enter numbers. Each of these covers a week. The workbook starts with only one week. A new sheet is added every week until the end at 13 or 14 weeks.

    Capture.PNG

    On the evening of a drawing, I enter the winning numbers into the six blank cells under the date of the drawing. Each of the blank cells has a name that is unique to the sheet (but is repeated on the other similar sheets). The red cells are named PA6, PB6, MA6 and MB6. I'm thinking that it would be good to capture the number of shares as I enter the red cell for each drawing.

    The captured value is used mainly in this summary sheet:

    CaptureA.PNG

    Right now it doesn't capture the Number of Shares for any specific drawing, but uses whatever the value is now. In other words, there were fewer shares back in the early weeks, but we've lost the amounts and are just using whatever the number is now. This isn't hugely important overall, since we post the Summary figures to our website after every drawing, but we DO lose track of historical figures in the workbook. I want to avoid having to use the website for history.
    Last edited by Lou Sander; 2015-05-26 at 17:05. Reason: typo
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Lou

    ..just as a matter of interest, save a copy of the file in .xlsb format.
    What size is it then????

    (NOTE: Excel .xlsb files can have macros in them)

    zeddy

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Zeddy,

    Maybe we should tell Lou about the .pdf camouflage trick?

  6. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Lottery31.xlsx - 4.60MB

    Lottery31.xlsb - 1.59MB

    I'd have to delete about 200 names and other contact information, but I don't think that would take too long.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Lou,

    Place the following code in the sheet module of the sheet from your second image. Assuming that you enter your number of shares after the lottery that night, when you update the cell named CountOfShares, column D will be search for the matching date of the current day then the share number will be copied into the adjacent cell in column G on the same row. Use this cell as the reference value for your dependent formulas located in other areas of your workbook that have need for this share value. The share number for that date will not change with new CountOfShares entries once you are past midnight. I also assume that the cells in the Number of Shares column for drawings that have not yet taken place are blank

    HTH,
    Maud


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("CountOfShares")) Is Nothing Then
            For I = 3 To 39
                If Date = Cells(I, 4)  Then
                    Cells(I, 7) = Range("CountOfShares")
                    Exit Sub
                End If
            Next
        End If
    End Sub
    This could have also been done with a match formula within the vb code
    Last edited by Maudibe; 2015-05-26 at 19:53.

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    or with the match formula:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("CountOfShares")) Is Nothing Then
            num = WorksheetFunction.Match(CLng(CDate(Date)), Range("D3:D39"), 0)
            Cells(num + 2, 7) = Range("CountOfShares")
        End If
    End Sub

  9. #9
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I've just gotten around to looking at this. I'm thinking it might be close, but maybe no cigar.

    CountOfShares changes independently of the drawings, depending on when people buy new shares. Typically it grows regularly through the first few weeks, then more or less settles down to a steady state. Even toward the end of the pool, though, people sometimes send in more money, which changes the CountOfShares.

    My main need is to have an unchanging number of shares plugged into the Summary sheet, so as time goes on and more shares are bought, the numbers for the past don't change. Absolute accuracy of the count on a given day isn't important. In other words, it's OK to use the count from a few days ago. I copy the numbers from the Summary sheet, and paste them into my website program, from which they are posted to the Internet HERE.

    The Summary sheet DOES change any time I update a drawing, so that's probably the sheet to monitor for changes. When it changes, the current number of shares needs to be plugged into it. Just where to plug it is discussed below.

    There are some complications that I didn't think about when I originally posted this problem.

    1. After every drawing, I post the winning numbers and the amount won to the website. This can be done on the day of the drawing, or usually the day after, since the drawing is at 11:00 PM, and I'm sometimes in bed. It can even be done a week or so later, if I'm out of town for a while.
    2. Also after every drawing, lottery officials tell us the amount of the Jackpot for the next drawing of that type, either Powerball or Mega Millions. As you may recall, the date of that drawing is known, but is a varying number of days from the current drawing, depending on the calendar. That number takes a while for lottery officials to calculate, so it's often not available until the next day. If I'm away, I won't post it for several days.
    3. It is really when I post THAT number that I want to capture the number of shares. As a practical matter, it's OK if the number of shares changes between then and the date of the drawing. A few shares one way or the other won't drastically change the amount a player takes home if we win, and the chance of us winning is infinitesimal anyway. The main need here is for that number not to change once it's recorded. (If we ever win a Jackpot, we will VERY carefully count the number of shares that were held at the moment of the drawing, identify their owners, etc.)

    The "number of shares" column on the summary sheet IS empty until something is posted there.

    All this may make for an impossible nightmare, but maybe not. Excel can do magic, and her gurus are very powerful. I'm not such a guru, but I'm thinking that whenever the summary sheet changes, the current number of shares should be plugged into the column to the right of the last nonzero Jackpot Cash Value, regardless of what is there right now. I think that would satisfy my needs.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Lou,

    Thanks for the explanation. I am assuming several things; please correct me if I am wrong or clear up a few questions.

    1. Assumption: A cell named CountOfShares holds the latest count of the shares
    2. Is the CountOfShares an inputted value or a calculated value?
    3. If it is an inputted value, what sheet and cell is it inputted?
    4. If it is calculated, what sheet and cell holds the formula for the value?
    5. Assumption: You want the CountOfShares at the time it is inputted or calculated to become a static value for calculations elsewhere
    6. Assumption: You want the static CoutOfShares value placed somewhere in column G on the summary sheet
    7. Using your summary sheet image above, if you "obtained" the CountOfShares 3 days after the drawing, what cells will the static value need to be placed? For example: the drawing was on 5/5/2015 and you obtained the CountOfShares (value=110) on 5/8/2015. Does the value get placed in Col G for 5/5/2015 (G23), 5/6/2015 (G24), and 5/8/2015 (G25)? And does the number of shares change between the PB and MM?

    Lou1.png

    If you input the CountOfShares, a worksheet_change event can be used. If you calculate CountOfShares then a worksheet_calculate event would be used. The code provided above assumed that you manually inputted CountOfShares and located somewhere on the summary sheet. It also assumed that you where inputting the value the night of the drawing before midnight. By your last explanation, this is not nessecarily the case.

    Have that cigar ready; we are closer than you think!

    Maud

  11. #11
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Good stuff!

    1. A cell named CountOfShares DOES hold the latest number of shares.
    2. There's a worksheet named !Participants, and the CountOfShares cell is on that sheet. When someone sends money, I enter their information on !Participants. One of those items is how many shares they have bought. It goes into a column named Current_Shares. CountOfShares is a cell that holds the total of all entries in Current_Shares.
    3. Not applicable
    4. Cell CountOfShares on sheet !Participants holds the formula =SUM(Current_Shares)
    5. I DO want CountOfShares to become a static value that I will use elsewhere, usually at a later date. That later date is whenever the Lottery releases the results of the latest drawing, which is shortly after 11:00 PM on the night of the drawing.
    6. I DO want the static value to be placed in Column G of the summary sheet. Actually it needs to go three places there: in the row corresponding to the date of the drawing, and in the two rows below that. That gives me an actual value for CountOfShares as of the date of the drawing, and pro forma values for the next two drawings.
    7. Your example here is correct. Normally I update everything on the date of the drawing ("everything" being the cells on other sheets whose values are picked up in the summary sheet). In that case, the static value would be the actual CountOfShares as of the date of the drawing. (Maybe that could be done automatically when the date of the drawing rolls around. That would REALLY be neat.) If I am really lax, and don't update anything until long after the drawing date, it's OK if the static value as of that time is used. If a few more shares have been sold in the interim, it doesn't really make much difference, since all of these are just pro forma values. If many shares were sold, I could just update the numbers manually. Finally, the number of shares is the same for MM and PB. Buying shares gives me the money to buy tickets, and I use my judgment in divvying the money between MM and PB.


    Continuing the idea presented above, it would be most excellent if, whenever a date on the summary sheet matches TODAY(), the current static value of the share count is plugged into Column G at the row corresponding to today's date on the summary sheet, and also into the two rows below it, overwriting any pre-existing values in any of those cells.

    That would always give an accurate value as of the drawing date, plus good estimates for the next two drawings. It wouldn't matter when I updated things, since the changes are controlled by the calendar, not by me.

    I have no idea if this is possible, of course, but I have faith in the power of Excel.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Normally I update everything on the date of the drawing ("everything" being the cells on other sheets whose values are picked up in the summary sheet).
    Lou,

    According to the above quote, when you update your data on the other sheets, the summary sheet is updated as well. The following code will look for a calculation event on the summary sheet. When the event occurs, a search for today's date in column D will be performed. When a match is found, it will place the value of CountOfShares from !Participants sheet into the row of the current date at Column G as a static value and the next 2 rows as well.

    ...whenever a date on the summary sheet matches TODAY(), the current static value of the share count is plugged into Column G at the row corresponding to today's date on the summary sheet, and also into the two rows below it.
    Place in the Summary Sheet module
    Code:
    Private Sub Worksheet_Calculate()
    Dim LastRow As Long, I As Long
            With Worksheets("Summary")
            LastRow = .Cells(Rows.Count, 4).End(xlUp).Row
            For I = 3 To LastRow
                If Date = .Cells(I, 4) Then
                    .Cells(I, 7) = Worksheets("!Participants").Range("CountOfShares")
                    .Cells(I + 1, 7) = Worksheets("!Participants").Range("CountOfShares")
                    .Cells(I + 2, 7) = Worksheets("!Participants").Range("CountOfShares")
                    Exit Sub
                End If
            Next
            End With
    End Sub
    Question: What should occur if you update your spreadsheet on an off day that there is not a drawing, hence, no matching date on the summary sheet?

    HTH,
    Maud

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    Lou Sander (2015-06-01)

  14. #13
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Thanks! There's no drawing tonight (Monday), so I'll test the code tomorrow when there is one.

    I will also ponder your question. It is an important one, since updates regularly occur throughout the week, with no consideration of whether there's a drawing tonight.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  15. #14
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    The more I ponder this, the thornier the situation becomes. Best will be if the gurus can see the whole workbook. I've put a depersonalized copy at a place where anyone can download it. ("Depersonalized" = all names have been removed or altered, all bank account numbers have been removed)

    To download it, go HERE and click the photo of the big pile of money.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Lou,

    I took a look at your workbook. Seems to me that if you updated the data and there is no drawing tonight than the CountOfShares should be placed in column G starting at the last occurred drawing date and the next 2 after it.

    When the sheet updates, this code looks for the latest date prior to or equal to today then places CountOfShares in the corresponding cell in column G and the next 2 cells beneath it.

    Lou2.png


    Code:
    Private Sub Worksheet_Calculate()
    Dim LastRow As Long, I As Long
        With Worksheets("Summary")
        LastRow = .Cells(Rows.Count, 4).End(xlUp).Row
        For I = 3 To LastRow
            If Date < .Cells(I, 4) Then
                Application.EnableEvents = False
                .Cells(I - 1, 7) = Worksheets("Participants").Range("CountOfShares")
                .Cells(I, 7) = Worksheets("Participants").Range("CountOfShares")
                .Cells(I + 1, 7) = Worksheets("Participants").Range("CountOfShares")
                Application.EnableEvents = True
                Exit Sub
            End If
        Next
        End With
    End Sub
    HTH,
    Maud

Page 1 of 2 12 LastLast

Posting Permissions

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