Dynamic Range


Worksheet "Example Weekly Backlog" - Tab "AWD Weekly Dashboard"

Cells f5:I have formula that will count (30-60) aged ranged in column H:H - Now I'm trying to also add a "Dynamic Range" like you suggest to match to "Blank" in Column N.

I would need the same formula for (60-90), 90-180, and 180>


Tab "AWD Weekly Dashboard" Cell A12-A40 - I need to pull out this data from the "Aged Data" tab found in Column N. I would like the data pulled into the C12-C40 cells. (Restrict 2 will be listed as 2 in column N on the "Aged Data" Tab. Not sure how to pull that over)



Selected Answer

Excel has made it very easy to crate named ranges in several ways but there is only one universal tool that can do everything. That is the Name Manager, available from the ribbon's Formulas tab. Beginners should start with familarising themselves with this tool.

To create a new named range, open the Name Manager and click New.

  1. Define a Name.
    The name allows no spaces. Use underscores if you must. I get done using upper case characters to mark the beginning of words.
  2. Define the Scope.
    Make the new range name available to the entire workbook, meaning you can use it in formulas on all sheets. If you know that you will use the name only on one sheet, limit the scope to that sheet. Formulas on other sheets will not have access to that name.
  3. Add a Comment (optional)
    You may write a description of the purpose for which you created the range. That may be helpful when other people work with the workbook.
  4. Define the range (in the Refers to field).
    Excel suggests the currently selected range by default. You can accept it or type another. Or you can enter a formula to define the range.
  5. Press OK to create the name.

Once created, the new range's name is listed by the Name Manager. Select it and click Edit ... or Delete.

To create a dynamic range use a formula like the one below in the Refers to field.

=OFFSET('Aged Data'!$A$2,0,0,COUNTA('Aged Data'!A:A)-1,14)

This formula defines a range starting 0 rows and 0 columns offset from A2 (effectively from A2). For some advanced uses you may want to calculate the offset, using more formulas. It's more useful to note that you can start your range at any cell.

The range has COUNTA('Aged Data'!A:A)-1 rows and 14 columns. Most ranges you may create will have only a single column. For the purpose of this tutorial I want to show how to handle a multi-column range.

COUNTA('Aged Data'!A:A)-1 is the formula which makes the range dynamic. As you add or delete a row the range will expand or shrink. The presumption is that column A has an entry in every used row, no blank rows. You can define another column to count entries in. The final -1 will not be required if A1 is blank (and therefore not counted by COUNTA). There are other formulas to use if you don't have a column without blanks.

In your worksheet, the above formula will create a range referring to $A$2:$N$33167 (33166 rows). I have named it AgeData. The formula COUNTA(AgeData) returns the number of used cells within the range. Observe that it will cause a circular reference (with a #REF error as result) if entered within the AgeData range.

In your AWD Weekly Dashboard, cell F5 you have this formula. =COUNTIFS('Aged Data'!$H:$H,">=30",'Aged Data'!$H:$H,"<60")+COUNTIF('Aged Data'!$N:$N,"blank"). Presuming that you had created two dynamic ranges, Ages (column H) and Unaged (column N), this formula would now look like this.


However, if you prefer - for whatever reasons - to name only a single range, using the one I created in this example, you would need to specify individual columns of it and the formula would look like this.


INDEX(AgeData,,8) specifies the 8th column of the range AgeData. In this case that is column H because the range starts in column A. Neddless to say, the column number could be calculated using another embedded formula. Note that within the context of the INDEX function the column is defined by the Area number argument, not the Column argument which serves, in conjunction with the Row argument, to define a single cell.



I've updated my sheet based on your notes - Used the name manager and the same formula's. 

Counts in F5:I5 are not calculating correctly? - Example F5 should be "6150"

Offset formula in cells C12:C40 are only pulling in "Data" - Example C12 should be "8"
Sroncey21 (rep: 62) May 6, '19 at 8:47 pm
The named ranges in your workbook aren't set up according to my instructions. It seems that you entered the formula which should go to define the named range in cell C12 instead. Since the formula returns a range its result can't be shown in a single cell (C12). I suggest you follow the instructions more precisely.
Variatus (rep: 3533) May 7, '19 at 5:48 am
I figured out the issue. I needed to add the range based on both criteria being true. I just put a comma between the two and presto. Sometimes you just have to work through your own problems. I appreciate all the help.
Sroncey21 (rep: 62) May 7, '19 at 4:01 pm
It appears to be counting both anything in the range of 30-60 and anything blank. I've been working on this for a while, I can't figure out how to 1) if it falls within the 30-60 range and also is a blank then it will only count as a match. I've tried numerous variations.
Sroncey21 (rep: 62) May 7, '19 at 7:28 pm
Your comment doesn't refer to the subject of this thread, i.e. declaration of dynamic named ranges.
Variatus (rep: 3533) May 7, '19 at 10:08 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login