+ Reply to Thread
Results 1 to 21 of 21

Date variation validation problem - a challenge needed help!

  1. #1
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Date variation validation problem - a challenge needed help!

    I’ve a spreadsheet to work out the weekly ranking points of players for the badminton tournaments.

    The rule is simple with the best 10 scores over a 12 month’s period. That works reasonably well when the tournaments are on a 12 months interval and on the same week every year but failed when the organisers decided to move the dates from previous year.

    From the example attached, the scores for the 12 months are in Col Q to Col DT. Col D to M work out the best 10 and sum them up in Col B.

    Let say the next 2 tournaments to play are German Open 2014 and All England 2014 that will replaced the same tournaments in 2013. What if the organisers decided to play the German Open 2 weeks earlier then the German Open 2014 score needed to replace the German 2013.

    Any suggestions as how to accommodate this would be much appreciated.
    Attached Files Attached Files
    Last edited by AlanY; 11-04-2014 at 07:42 AM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: A challenge needed help!

    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 go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  3. #3
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: A challenge needed help!

    Alan, first of all I'd suggest you change the title of your thread before you get hit with an infraction notice Like me earlier!

    To change a Title go to your first post, click EDIT then Go Advanced and change your title... try something like "Date variation validation problem" and you should get away with some answers from other contributors!


    Pete
    Pete
    Struggling Solutions Architect

  4. #4
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: A challenge needed help!

    ah well, I tried to get the warning in first...

  5. #5
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: A challenge needed help!

    No problem dubcap01.
    We are all here with same goal: To make this Forum better place for all

  6. #6
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: A challenge needed help!

    Quote Originally Posted by dubcap01 View Post
    Alan, first of all I'd suggest you change the title of your thread before you get hit with an infraction notice Like me earlier!

    To change a Title go to your first post, click EDIT then Go Advanced and change your title... try something like "Date variation validation problem" and you should get away with some answers from other contributors!


    Pete
    thanks.
    I did tried to find a better title with no avail.

    thanks for the suggestion as well

  7. #7
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: A challenge needed help!

    Quote Originally Posted by zbor View Post
    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 go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    oops...
    saved by the bell, that was my 9th post.

  8. #8
    Forum Contributor
    Join Date
    06-23-2014
    Location
    Dubbo, Australia
    MS-Off Ver
    EXCEL 2019
    Posts
    180

    Re: A challenge needed help!

    Alan and Zbor - like

  9. #9
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: Date variation validation problem - a challenge needed help!

    any suggestion would be much appreciated

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Date variation validation problem - a challenge needed help!

    Maybe this:

    Select year (yellow cell) what you want to use and every tournament need to have year in the title.
    Attached Files Attached Files

  11. #11
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: Date variation validation problem - a challenge needed help!

    thanks,

    don't think select year going to work as the period needed to be in any 12 months.

  12. #12
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Date variation validation problem - a challenge needed help!

    So in any 12 months if the tournament isn't same?

    Basically you select two columns and all data inside if not duplicated tournament?

  13. #13
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: Date variation validation problem - a challenge needed help!

    Quote Originally Posted by zbor View Post
    So in any 12 months if the tournament isn't same?
    yes, that's the main problem if they decided to move one tournament a few weeks ahead then we have 2 in a 12 months period and the latest preceded the earlier one.

  14. #14
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Date variation validation problem - a challenge needed help!

    Here.

    Only use same format: Tournament name Year.

    So instead of:
    Please Login or Register  to view this content.
    use

    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: Date variation validation problem - a challenge needed help!

    thanks, will have a look later.

    am suppose to be at work

  16. #16
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: Date variation validation problem - a challenge needed help!

    zbor, again thanks for your suggestion.

    i'm sure the helper row as suggested is the solution.
    the problem is they can even change the tournament name, e.g. if they change the sponsor thus Yonex German Open 2013 may becomes Wilson German Open 2014 within the 12 months period.

    why life is so complicated!

  17. #17
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Date variation validation problem - a challenge needed help!

    Then I see only one way:

    Use helper sheet.
    Enter there all tournaments and they "CODE NAME".
    That code name might be like Tournament at the first but as you change sponsor IT SHOULD STAY SAME!

    For example:

    Please Login or Register  to view this content.
    Attached Files Attached Files

  18. #18
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Date variation validation problem - a challenge needed help!

    Of course, you can use your own code from the start:
    Please Login or Register  to view this content.
    But you need to add every new tournament into that sheet.

  19. #19
    Valued Forum Contributor
    Join Date
    08-04-2011
    Location
    UK
    MS-Off Ver
    Excel 2021
    Posts
    346

    Re: Date variation validation problem - a challenge needed help!

    many thanks for your help, i think i'm getting there.

  20. #20
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Date variation validation problem - a challenge needed help!

    Oh, no. I'ts not OK.. I need to look a little more but looks like VBA is needed.

  21. #21
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Date variation validation problem - a challenge needed help!

    Here... I've add one more calculation in 2nd sheet.
    So, you need to:

    Enter tournament (in 2nd sheet and in first sheet 2nd row)
    Enter code, unique for each tournament
    Extend formula in C and D column down.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Help Needed to Extract Data from Text - Newest Challenge
    By dbsbender in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-31-2014, 09:34 AM
  2. Up for a challenge? Roullete simulator macro needed
    By Butcher1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2014, 01:00 AM
  3. [SOLVED] if/then challenge
    By matt5596 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2014, 01:04 PM
  4. A Challenge
    By jimbob in forum Excel General
    Replies: 17
    Last Post: 04-01-2006, 05:37 PM

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