Dynamic ranges
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Dynamic ranges
In the Formulas>Name Manager, how could one tell if this/those listed are dynamic?
-
- Administrator
- Posts: 78484
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Dynamic ranges
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.
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
Hans
-
- BronzeLounger
- Posts: 1389
- Joined: 08 Jul 2016, 18:53
Re: Dynamic ranges
Thanks for the explanation.