Performance question regarding named range

[YounesB3]

Hello, what is less heavy between :

A) =INDIRECT("Sheet1!$A$1:$Q$"&counta(Sheet1!$A:$A))

B) =Sheet1!$A$1:$Q$20000

when you reference to this named range?

Does it depends on the amount of formulas referencing to the above, or is one strictly always better then the other?

 

[Fazza]

hello

The INDIRECT formula has a calculation overhead. If you've only one such formula, it isn't much of an impact: if you have lots, it can be. (If relevant, such a dynamic named range is not recognised when doing queries - such as databases use, with SQL, etc.)

The other definition is lean & mean. Though excessive if using 20000 just to cover an 'always big enough' range that is far more than you really need.

Another approach if the second formula need only cover the rows you actually have, is to use VBA to update the defined name. Such as where a data range is on a worksheet, use

Private Sub Worksheet_Deactivate()
me.range("A1").CurrentRegion.Name = "YourDataRangeName"
End Sub

 

[YounesB3]

hello

The INDIRECT formula has a calculation overhead. If you've only one such formula, it isn't much of an impact: if you have lots, it can be. (If relevant, such a dynamic named range is not recognised when doing queries - such as databases use, with SQL, etc.)

I have around 12k formulas which use the referencing, but once they are calculated, I paste them as values in my code.