Search backward in range till empty cell

XOR,

I love your formula - I want the last streak just as your formula gives.

I was wondering if you could expand this just a bit. MY problem is that the range of data to apply your formula is in the middle of a giant worksheet and the last column I want to search changes each week.

So I need to bound your formula (now bounded by $DW$9) with the last used column in a range within lots of other stuff. I assume I need the offset function and I've made some progress:

I did figure out how to find the last used column within my range:

=MAX(COLUMN($D$4:$AM$8)*($D$4:$AM$8<>""))-COLUMN($D$4)+1

But I can't figure out how to apply this to your formula

[Spreadsheet Attached]