Dynamic ranges

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Dynamic ranges

Post by bknight »

In the Formulas>Name Manager, how could one tell if this/those listed are dynamic?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dynamic ranges

Post by HansV »

Look at the Refers To property of the names.
If it contains a fixed value or range, or a formula that produces a fixed result, the name is not dynamic.
But if it contains a formula that contains COUNT, COUNTA or MATCH, the result will vary, making the name dynamic. A typical example looks like

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

This returns a range beginning in A1 on Sheet1, but the number of rows is the number of non-empty cells in column A. So if column A has 4 non-blank cells, the name refers to Sheet1!A1:A4, but if column A has 205 non-blank cells, it will refer to Sheet1!A1:A205.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Dynamic ranges

Post by bknight »

Thanks for the explanation.