Results 1 to 14 of 14
  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

    Lottery Winnings Summary Sheet

    Here's a worthy challenge, offered at the start of a worthy new year:

    At 1.85 MB, WinnersText.xlsm is too big to upload to the Lounge, so you can go HERE to download it. It consists of the pertinent sheets from a Workbook that I use for keeping track of things in a 100-player lottery pool. I've been using versions of this Workbook for many years, during which time it has evolved considerably, thanks greatly to help that came from this lounge. These sheets are for a pool that has just finished. NOTE: For various reasons, sheets wk1, wk2, and wk14 are anomalous, so aren't good choices for experimentation.

    The Winners sheet is a new one, and is under development. It's used to keep track of which lottery tickets were winners, how much they won, etc. It works fine when the pool is over and sheets wk1 through wk14 exist, as they do in this sample workbook.

    But it crashes mercilessly when a sheet is missing, with #REF! cells appearing all over the place.

    That's a problem, because the wkN sheets are created individually throughout the progress of the pool. As a week finishes, I click the Make New Sheet button (close to D11 on the current week's sheet). That copies wk0 onto a new sheet, making some alterations along the way. I can't create wk1-wk14 ahead of time, because wk0 changes significantly during the course of the pool.

    The Winners sheet is mostly useful after a pool is over. I can leave it off the workbook until all its weeks are finished, then copy it from a previous pool's workbook, make some changes, and have it work. (The changes involve removing the references to the workbook from which it was copied.) That's a bit tacky, and it doesn't give me any information during the progress of the pool.

    I have a solution to the #REF! problem. If, for example, in G5 of Winners, I replace ='wk1'!AA20 with =IFERROR(INDIRECT("wk1!AA20"),"-"), that will fix things (for that cell only) if wk1 is missing. It has the added benefit of putting a dash in the cell if it would otherwise be empty.

    It's a great solution, except that the change would have to be made in each of the 5,400 cells that make up the four tables on the worksheet.

    I suppose there's an easy way to do that, but I don't know what that might be. Or maybe there's another solution entirely. (I've thought of putting everything into a Word table, making some changes there, then pasting everything into Excel.)

    Who has any ideas?
    Last edited by Lou Sander; 2016-01-01 at 12:16. Reason: Name the file
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,205
    Thanks
    49
    Thanked 989 Times in 919 Posts
    You can use the regular expressions of Replace to change your cells.
    Search for this: 'wk[:digit:]+.*
    Replace with this: IFERROR(INDIRECT("\0"),"-")

    cheers, Paul

  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 don't follow you at all. Am I to use the Find and Replace dialog box?

    If so, am I to enter 'wk[:digit:]+.* verbatim? (When I try it, "Excel cannot find the data I'm searching for." Maybe it is shorthand for something else that I'm not getting.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,205
    Thanks
    49
    Thanked 989 Times in 919 Posts
    My bad, assumed a little more knowledge on your part.

    Yes, use Find and Replace.
    You need to use wildcards
    .
    Yes, enter the find string verbatim.

    The search expression works like this.
    Find "'wk", a digit, we want one or more digits so include a plus, now we want everything else so it's a dot and asterisk.
    Replace uses the first found text using the substitution characters "\0" and puts the bits we want around the outside. Substitution characters are numbered for multiple finds, e.g. \0, \1, \2...

    cheers, Paul

  5. The Following User Says Thank You to Paul T For This Useful Post:

    Lou Sander (2016-01-03)

  6. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Well, that wildcard thing opens up a whole new world!

    It rings a faint bell... maybe I've run into it before, very long ago. It wasn't mentioned in my various Excel books, but I guess they can only go so far.

    I will give it a try.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #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
    I'm working in Excel 2010. I don't find the "Advanced Find" stuff on my ribbon. It IS on the ribbon in Word 2010, however.

    Do I maybe need to turn my Excel table into a Word table, make my changes, then paste it into Excel? (I'm guessing that might work.)

    UPDATE:

    I tried it in Word on a piece of one of the tables, after using CTRL+` to get the formulas in the cells. I get this error:

    Capture.JPG
    Last edited by Lou Sander; 2016-01-03 at 07:28.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  8. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,205
    Thanks
    49
    Thanked 989 Times in 919 Posts
    I don't have Excel so I'm relying on the MS pages here (tested in LibreOffice), but I can't find a reference to regex in Excel except in VBA. Maybe MS omitted it?
    Can you post a screen shot of the Find/Replace dialogue?

    cheers, Paul

  9. #8
    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'm pretty sure I've fixed my problem. I used a "brute force" method of fixing the formulas column by column, and it seems to be working fine.

    I'm real happy to have learned about the wildcard stuff in Word.

    I'll be happy to post the screen shot you asked for, if you still need it.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  10. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,205
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Only to know if Excel supports regex in find/replace.

    cheers, Paul

  11. #10
    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 can't figure out how to get a screen shot of the Find controls, which involve clicking down arrows to get to the next step. When I try to get the screen shot, the drop-down menu disappears.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  12. #11
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,205
    Thanks
    49
    Thanked 989 Times in 919 Posts

  13. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Excel doesn't support Regex in Find/Replace I'm afraid. Has annoyed me for years.
    Regards,
    Rory

    Microsoft MVP - Excel

  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
    Great Scott, what a resource is the Lounge!

    I hope this is what you need (from Excel 2010, running under Windows 7)...

    CaptureDropDown.JPG

    CaptureFindAndReplace.JPG
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  15. #14
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,205
    Thanks
    49
    Thanked 989 Times in 919 Posts
    Quote Originally Posted by rory View Post
    Excel doesn't support Regex in Find/Replace I'm afraid. Has annoyed me for years.
    That explains this.
    http://www.codedawn.com/excel-add-ins.php

    cheers, Paul

Posting Permissions

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