Way to clean up Dynamic Index (dirty/volatile)

[vianen]

Hello all,

I've got a table with date and time of sportmatches being played. In another sheet I would like to have the most 7 upcoming games.

a short/simple example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A

[/TD]
[TD="align: center"]B

[/TD]
[TD="align: center"]C

[/TD]
[TD="align: center"]D

[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]Round

[/TD]
[TD]Date
[/TD]
[TD]Time
[/TD]
[TD]yearfrac
[/TD]
[TD]key/row#
[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: right"]11 aug
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]0,005555556
[/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD="align: right"]12 aug
[/TD]
[TD="align: right"]20:45
[/TD]
[TD="align: right"]0,002777778

[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD][/TD]
[TD="align: right"]12 aug
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]0,002777778

[/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD][/TD]
[TD="align: right"]13 aug
[/TD]
[TD="align: right"]20:45
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD][/TD]
[TD="align: right"]15 aug
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]0,994444444

[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD="align: center"][/TD]
[TD="align: right"]13 aug
[/TD]
[TD="align: right"]20:30
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6
[/TD]
[/TR]
[TR]
[TD]19

[/TD]
[TD]

2​

[/TD]
[TD="align: right"]19 aug
[/TD]
[TD="align: right"]20:45
[/TD]
[TD="align: right"]0,983333333
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD][/TD]
[TD="align: right"]18 aug
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]0,986111111
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD][/TD]
[TD="align: right"]19 aug
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]0,983333333
[/TD]
[TD="align: right"]9
[/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD="align: center"][/TD]
[TD="align: right"]19 aug
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]0,983333333
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD][/TD]
[TD="align: right"]19 aug
[/TD]
[TD="align: right"]20:30
[/TD]
[TD="align: right"]0,983333333
[/TD]
[TD="align: right"]11
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD][/TD]
[TD="align: right"]20 aug
[/TD]
[TD="align: right"]20:30
[/TD]
[TD="align: right"]0,980555556
[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD]25
[/TD]
[TD][/TD]
[TD="align: right"]21 aug
[/TD]
[TD="align: right"]20:30
[/TD]
[TD="align: right"]0,977777778
[/TD]
[TD="align: right"]13
[/TD]
[/TR]
</tbody>[/TABLE]
yearfrac =>
MOD(YEARFRAC($H$2-1;EDATE(B13;-12));1)
$H$2 = date today (14-aug).
C13 = column with dates.

As you can see the dates are not sorted.

On the first sheet I have this overview of the upcoming matches.
A short/simple example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]G

[/TD]
[TD="align: center"]H

[/TD]
[TD="align: center"]I

[/TD]
[TD="align: center"]J
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Date today:
[/TD]
[TD]14-aug
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Biggest
[/TD]
[TD]row#
[/TD]
[TD]Date
[/TD]
[TD]Time
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]0,994444444
[/TD]
[TD]5
[/TD]
[TD]15-aug
[/TD]
[TD]INDEX($C$13:$C$25;H4)
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]0,986111111
[/TD]
[TD]8
[/TD]
[TD]18-aug
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]0,983333333
[/TD]
[TD]7
[/TD]
[TD]19-aug
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]0,983333333
[/TD]
[TD]9
[/TD]
[TD]19-aug
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]0,983333333
[/TD]
[TD]10
[/TD]
[TD]19-aug
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The first (5) I find with the formula =MATCH(G4; D$13:D$25; 0).
That one is easy. For the corresponding dates I'm using INDEX as you can see.

But here it comes. For the second upcoming match I first used OFFSET but i learned that's a volatile formula so I changed this with INDEX.
But still when I opened and closed the file i had the 'save?' question so something was still volatile.
But then I learned on http://www.decisionmodels.com/calcsecretsi.htm that "Using INDEX as the second part of a range reference, for example A$2:INDEX(A$2:A$8,7,), will also cause the reference to be flagged as 'dirty' when the workbook is opened".
AH! that was the explanation I was looking for.

So for the second, third, etc.. upcoming match I'm now using a sort-of-dynamic INDEX instead of OFFSET.
the formula in H5 and lower = MATCH(G5;INDEX(D$13:D$25;H4+1):D$25;0)+H4

Is there another way to change this formula ( "INDEX(D$13:D$25;H4+1):D$25" ) so it is not dirty anymore and Excel wont recalculate with every trigger.


Ciao!
Ricardo

 

[RoryA]

Unlike OFFET, that won't recalculate with every trigger, only when the workbook opens, or its precedent cells actually change.