+ Reply to Thread
Results 1 to 4 of 4

Formula or Function to compare dates and calculate relevant cells

  1. #1
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    26

    Formula or Function to compare dates and calculate relevant cells

    Hello,
    I am stuck to continue with the existing formula.

    Goal: Compare dates in 2 differrent tabs and only calculate when there is a match.

    In my tab "Rating" in column "M", there is a formula. The last cell where you can see the formula is M1467. Right now, I need to manually change the data in that formula when there is a new date available. M1467 is showing the last entry which is using the date 22.Nov 2023.
    So, when I run the spreadsheet for the next day (in this case it would be 23.Nov 2023), I will have to change the following data inside the formula:
    $B$57:$B$78
    $A$57:$A$78
    $C$57:$C$78
    $A$57:$A$78
    and
    $E$76:$E$112
    $I$76:$I$112
    $F$76:$F$112
    $I$76:$I$112

    Those data are linked to tab "Filter_Min_Pts_Rtg". That data is adjusted daily. The new data lines are being put into tab "RangeData".
    In example to the above:
    In cell D5 (RangeData) you can find the nbr 57. That nbr correspondense with $B$, $A$$, $C$, $A$ (as seen in the section above) .So it does the same with the reamining three nbrs.

    Is it somehow possible to use a function or formula that uses the cell information as coordinates in my long formula to avoid manual adjusting. (which is quite errors vulnerable). I know it is possible to use the function "indirect". But I am somehow not able to get it done correctly.

    So, the cell information found in tab "RangeData" are corresponding to as follows (as per above sample):

    $B$57:$B$78 --- tab "RangeData" D5 & E5
    $A$57:$A$78 --- tab "RangeData" D5 & E5
    $C$57:$C$78 --- tab "RangeData" D5 & E5
    $A$57:$A$78 --- tab "RangeData" D5 & E5
    and
    $E$76:$E$112 -- tab "RangeData" G5 & H5
    $I$76:$I$112 --- tab "RangeData" G5 & H5
    $F$76:$F$112 -- tab "RangeData" G5 & H5
    $I$76:$I$112 --- tab "RangeData" G5 & H5

    The goal should be that I do not have to touch the formula in M1467 anymore and simply can copy it down when new data are being added. Respectively, when there is a change in values found in "RangeData" D, E, G & H, that the formula uses that new info and adjust the calculation by itself where applicable.

    The spreadsheet "Matchday.xls" is enclosed.

    I hope it understandable what I am describing.

    Thank you very much for your kind assistance. It is really appreciated as I am quite stuck at this moment...
    Thomas
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    45,474

    Re: Formula or Function to compare dates and calculate relevant cells

    Try like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    26

    Re: Formula or Function to compare dates and calculate relevant cells

    Hi Trevor,
    thank you very much you took the time looking into my query.
    I used your solution on all matters and it worked nicely. Thank you very much for that.
    However, there is the issue that the $D$5 and $E$5 is fixed and I received the #NV error when copying the formular to the next set of data the following next day. How can that be solved?
    Because when taking out the $, it would copy wrongly, since each day has a differrent length of datalines.
    Thank you very much
    Thomas

  4. #4
    Registered User
    Join Date
    09-16-2023
    Location
    Spain
    MS-Off Ver
    365
    Posts
    26

    Re: Formula or Function to compare dates and calculate relevant cells

    Becasue the following next day will have to read the data from D6, respectively E6...

+ 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. Formula to compare dates while identifying blank cells
    By Muke41 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-18-2023, 04:20 PM
  2. [SOLVED] Help with formula to calculate no. of cells within dates
    By techrookie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-20-2023, 10:37 PM
  3. [SOLVED] Need to compare two documents for relevant data!
    By KeeganP in forum Excel General
    Replies: 9
    Last Post: 04-08-2021, 12:41 PM
  4. VBA Formula to show task list relevant to event dates across worksheets
    By Sawfish in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-21-2016, 02:07 AM
  5. [SOLVED] Need to get my formula to check dates and use relevant one
    By Gavalar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-18-2014, 05:17 PM
  6. Replies: 9
    Last Post: 10-17-2012, 10:33 PM
  7. [SOLVED] Using IF function to compare dates of two cells
    By Mr.WalterJones in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-12-2012, 11:15 AM

Tags for this Thread

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