CODEWITHSUNDEEP
excelvbaexcel-formulaspill-range
PotterFan
PotterFan

Reputation: 87

IF statement Not Recalculating Past Length of Referenced Spill Range in Excel

I have a column that is a spill range that uses the filter and sort_by formulas. This spill range is dynamic and I have an IF statement that references the spill range but upon recalculation of the sheet the IF statement doesn't recalculate past the length of the old spill range. Here is the spill range formula:

=SORTBY(
    FILTER(
        'Loss Template'!$E:$E,
        ('Loss Template'!$A:$A=TEXT($B$2,"0"))*('Loss Template'!$F:$F<=$J$7)*('Loss Template'!$F:$F>=$H$7)*('Loss Template'!$H:$H>2000)*('Loss Template'!$M:$M=5),
        ""
    ),
    FILTER(
        'Loss Template'!$M:$M,
        ('Loss Template'!$A:$A=TEXT($B$2,"0"))*('Loss Template'!$F:$F<=$J$7)*('Loss Template'!$F:$F>=$H$7)*('Loss Template'!$H:$H>2000)*('Loss Template'!$M:$M=5),
        ""
    ),
    1,
    FILTER(
        'Loss Template'!$H:$H,
        ('Loss Template'!$A:$A=TEXT($B$2,"0"))*('Loss Template'!$F:$F<=$J$7)*('Loss Template'!$F:$F>=$H$7)*('Loss Template'!$H:$H>2000)*('Loss Template'!$M:$M=5),
        ""
    ),
    1
)

Here is the IF statement:

=IF(
    $G10 <> "",
    IF(XLOOKUP($G10,'Loss Template'!E:E,'Loss Template'!G:G,,0)="Closed", "F", "O"),
    ""
)

I found out if I use Find and Replace "=" with "=", then it fixes the issue.

Should I code a workaround in my update_macro doing this? Or how can improve these formulas?

Note: I need an Update_macro because it is on manual calculation.

Upvotes: 0

Views: 53

Answers (1)

PotterFan
PotterFan

Reputation: 87

I tried using the Spill Range identifier(#) and it works so far. thanks everyone for the help! Credit to @Scott Craner for the answer.

=IF(XLOOKUP($G10#,'Loss Template'!E:E,'Loss Template'!G:G,,0)="Closed", "F", "O")

Upvotes: 0

Related Questions