+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : Global number tracker based on position.

  1. #1
    Registered User
    Join Date
    03-22-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Global number tracker based on position.

    Hi there!
    I'm making a spreadsheet for a card room, and I'm having some trouble figuring out how to format some of the functions.

    I'd imagine this is quite simple, but I'm new to excel.
    There are a few things I'm trying to figure out, but I think it's a solve one solve all kind of thing.

    On the right hand side, I want a global stats tracker for each game type, and some combined totals.
    Here's a screenshot of what I have so far, and some badly drawn circles to illustrate what I'm trying to say:Excelhelp.jpg

    All games with the limit 1e/2e should have their total (E21, for 1st of jan) added to S13
    Games of 2e/4e should have their total added to S14, and so on.

    I'm trying to figure out how to specify the totals for different games, how to distinguish the total for the 1e/2e game on the first of jan from the 2e/4e on the second, and add them accordingly

    Then I want to add the total money on the table (Fig. 2, cell C6,) to the total in S4 regardless of game type, be it 10c/20c or 5e/10e.

    Of course I can do some sort of annoying autosum hold ctrl thing, but I want to be able to scale this for all 365(6) days of the year.

    If you can show me how to do this, I'll just replicate it to fit the other areas of the stats tracker.

    Any help would be much, much, MUCH! Appreciated! :-D

    All the best,
    Mark.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Global number tracker based on position.

    It will make it much easier for us to help you if you upload a sample file with dummy data, rather than a picture. That way, we can play around with what you have, to make a better solution for you
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-22-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Global number tracker based on position.

    Quote Originally Posted by FDibbins View Post
    It will make it much easier for us to help you if you upload a sample file with dummy data, rather than a picture. That way, we can play around with what you have, to make a better solution for you
    Ah, here you go excelhelp1.xlsx

    Hope that does the trick.
    Again, thanks.

    EDIT: Just noticed it's auto including that file below. Please use the excelhelp1 file.
    Attached Files Attached Files
    Last edited by theelous3; 03-22-2012 at 08:11 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Global number tracker based on position.

    Thanks. Looking at your sheet, is there any reason that you have set it up that way? I think I would have put all the headings (cash, limit etc) as column headings across the top, instead of downwards, and have your date - and data on a single line - you dont have to type/copy them every time, and it will probably make your summary easier too

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Global number tracker based on position.

    As start, try the attached, and see how much easier data entry and calcs would be. If you cannot use this layout, let me know and we can try a different approach
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-22-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Global number tracker based on position.

    Quote Originally Posted by FDibbins View Post
    As start, try the attached, and see how much easier data entry and calcs would be. If you cannot use this layout, let me know and we can try a different approach
    That is fantastic. Allows for a far more elegant approach to counting game types too.
    So, on to moving it to a seperate sheet.
    Rep +1, and though I've said it about four times by now thank you :-)

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Global number tracker based on position.

    Glad to help, and I hope I showed a different approach for you for other projects you may have. let me know if you have any other questions

    If this is solved, please mark it solved

  8. #8
    Registered User
    Join Date
    03-22-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Global number tracker based on position.

    I was wondering how I put it on a different sheet? How do I refrence the first sheet? All that jazz.
    Cheers.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Global number tracker based on position.

    1 real quick way is to cut/paste it. Highlight the entire table, press ctrl x (cut), move to the sheet you want it to be, press ctrl v (paste) Once you have done that, take a look at how the formulas have changed, so that you can add others using the same "format".

    let me know if you need more help

  10. #10
    Registered User
    Join Date
    03-22-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Global number tracker based on position.

    Getting #REF! Errors in all the boxes with functions. Will attach.
    Do I have to use a ! modifier to send it to a different sheet?
    excelhelp1(2).xlsx

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,242

    Re: Global number tracker based on position.

    looks like you copy/pasted instead of cut/paste use ctrl x (not ctrl c) or rt click-cut, then try it again. 1 way to be sure you did it roght, if you look back to where the table was...if its still there, you copied instead of cut.

    the formula in daily input sheet....=COUNTIF(D:D,K14)
    in stats tracker changes to.........=COUNTIF('Daily Input Sheet'!D:D,A8)

    keep me informed

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1