Not using OFFSET in Dynamic Named Ranges......?

[Clearwell_T]

Hi All,

I’m sure it’s a dirty word or grubby topic in some quarters around here, but I’ve been using ChatGPT to help me learn how to work with more advanced formulas, and also handle some particularly difficult areas of a sheet I’m building.

Having got that out there, I’m painfully aware of how wrong the AI can be sometimes, so I love the fact that sometimes a good old human expert master can be just the tonic and a welcome sanity check needed.

Before I go ahead and reformat my entire huge spreadsheet along the lines described below, can anyone with skills better than mine comment on these statements from ChatGPT (o4-mini-high)?

Cheers.

CT

To avoid the performance hit of scanning a million rows (or even running COUNTIF over tens of thousands of cells), you can redefine your named range without any volatile functions and by only ever touching your fixed 21:10485 block. Here’s how:

1. Dynamic Named Range via INDEX–LOOKUP

Instead of OFFSET+COUNTIF, use two INDEX() calls separated by a colon—one to find the first non‑blank (“>0” or “EXCLUDED”) and one to find the last—with MATCH() and a LOOKUP() trick, respectively.

2. Why This Is Fast

• Non‑volatile: No OFFSET(). The only functions are MATCH(), LOOKUP(), INDEX(), and a handful of simple string tests, all of which only recalc when their precedents change.
• Limited scan: You only ever scan the fixed block DA21:DA10485 (and Z21:Z10485), not the entire column or worksheet.
• Auto‑adjusting: As soon as a formula in DA21:DA10485 changes from "" to ">0" (or vice versa), your start and end pointers shift, and the named range resizes itself on the fly.

By keeping your DNR definition focused only on the bounds of your data (i.e. cutting out the leading/trailing blanks), you get a small, tight range. Then you layer whatever content‑based filters you want in the formulas that use that range. That separation of concerns:

1. Keeps the named range itself fast and simple (it never changes unless you add truly blank rows at the top or bottom).
2. Gives you maximum flexibility—if tomorrow you need to test for “<5” or for dates in a given month, you just swap out the test in your MATCH or SUMPRODUCT without touching the DNR.

Efficiency gains

1. Non‑volatile recalcs
   INDEX, MATCH and LOOKUP only recalc when their direct precedent cells change. You avoid the “every change, everywhere” cost of OFFSET().
2. Bounded scans
   You only ever look at CR21:CR10485 (or DA21:DA10485), not the entire column. That can be thousands of rows faster than scanning 1 048 576 rows or an entire sheet.
3. Automatic resizing
   As soon as your formulas in that block flip between "", ">0", or "EXCLUDED", your named range’s start and end pointers shift immediately—no helper cells or manual tweaks required.

[koyotak]

sumproduct() offers no computational advantage over concatenated individual cells. The particular advantage is that the Sumproduct() function can be created quickly, easily, and error-free, whereas the concatenation [A1][B1]+[A2][B2]+… is error-prone, time-consuming, and not convenient. If you put this function in a function block {=sumproduct()}, the microcontroller knows that it only has to “look at” and thus “process” this specified range when a change occurs. If nothing has changed in the block, it skips this block and searches for a change step by step/bit by bit/function line by line. Do you see the advantage of multiple blocks over many “loose” functions?

[koyotak]

I cannot see how the ‘=LET()’ function is more effective than anything else. The only thing is that it assigns and uses a name for a variable in an otherwise nameless accumulator area. The accumulator is a construct in which values are stored until the next operation overwrites them and the original value is lost. By assigning a unique name to the variable, it is stored in a non-volatile memory as long as the process is running or the system is enabled. The accumulator cannot store data; its cells are reassigned with each arithmetic operation. This is my basic knowledge from C++/PLC

---

Correction: Microsoft support states that the calculation ‘=LET()’ is twice as fast as without ‘=LET()’ by using ‘=IF()’ - in an unfortunately faulty solution when abusing EXCEL as a database.
This won’t make me happy!

---

My attempt convinces me to close the matrix area with ‘{=function()}’ instead of ‘=LET()’, in which I do not have to assign an implicit name.

[koyotak]

The ‘=LOOKUP’ function is a very simple comparison operation that uses few processing resources. Unfortunately, it’s extremely restrictive, limited, and absolutist; you have to understand its simplicity. I like it precisely because it reacts stubbornly with #NV. The combination ‘=MATCH’ & ‘=iNDEX()’ is more flexible and therefore less resource-intensive.