# Formula or Function to compare dates and calculate relevant cells

1. ## 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

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

Try like this:
Formula:
`Please Login or Register  to view this content.`

3. ## 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. ## 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...

##### Users Browsing this Thread

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

#### 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